Web Development

Ask something
Discussion Locked

SQL Server 7.0 query problem

0 helpful
9 replies

by ohjani

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

i get this error when i tried to INNER JOIN 2 Views (each view contains a few sub queries/views). pls tell me what is the problem.

"Could not allocate table for query optimization. Maximum number of tables in a query (256) exceeded."

i'm sure the total amount of tables involved in all the queries doesn't exceed 256. each of the views prob has abt not more than 10 sub views. therefore the total number of all involved tables shouldn't exceed 20.

btw, how do i turn off Query Optimization? i've unchecked the Auto Create Statistic and Auto Update Statistic in the database properties.

9 replies

by amanabala

Hi,

Can you send me the entire query string? I will try to replicate at my site and find a solution for you. You can write to my personal email , if you choose to.

Thanks and good luck
-Ravi

by ohjani

I'm sorry but i've reconstructed the whole queries and views, therefore i can't send you any codes.
I afraid this problem will arise again in the future but at the moment i have no codes to let u simulate the situation.

by pikikoko

Look at your SQL Server Properties and increase the query governor specified cost settings. You may be exceeding your specified cost. This error message may be a result of this. The limit is 256 tables but this may be triggered by your server query cost being exceeded.
Try it.
Good luck.
Mike

by lw

You could try selecting into a temp table from each view, then selecting from the two temp tables using the inner join, which would probably prove to be quicker also. Views are horrible!

by ae_maillist

Create a stored procedure and break down the handling in small (fast) steps...

And (yes) use a temporary table to show everything in the end.

This discussion is closed to new replies.