System Error 4414 (Exceeded maximum number of tables in a query)

System Error 4414 (Exceeded maximum number of tables in a query)

Post by Vasu » Wed, 24 Sep 2003 03:32:45



Hi,
 I have a master view based on about 30 child views. When
i run the SQL script, my view is created fine without any
errors.

When i try to SELECT from my master view, i am getting the
following Error:
////////////ERROR////////////////////
Could not allocate ancillary table for view resolution.
The maximum number of tables in a query (256) was
exceeded. (State:37000, Native Code: 113E)
//////END ERROR/////////

Can anyone help me out...

Thanks,
Vasu

 
 
 

System Error 4414 (Exceeded maximum number of tables in a query)

Post by Greg Linwoo » Wed, 24 Sep 2003 03:50:20


If you're on SQL EE, you might be able to get away without any re-design by
materializing some of the sub views?

Regards,
Greg Linwood
SQL Server MVP


Quote:> Hi,
>  I have a master view based on about 30 child views. When
> i run the SQL script, my view is created fine without any
> errors.

> When i try to SELECT from my master view, i am getting the
> following Error:
> ////////////ERROR////////////////////
> Could not allocate ancillary table for view resolution.
> The maximum number of tables in a query (256) was
> exceeded. (State:37000, Native Code: 113E)
> //////END ERROR/////////

> Can anyone help me out...

> Thanks,
> Vasu


 
 
 

System Error 4414 (Exceeded maximum number of tables in a query)

Post by Louis Davidso » Wed, 24 Sep 2003 04:45:48


First let me say, you rule.  I have never even come close to this limit in
my most normalized databases.  I hope that is why you came close :)

What I would suggest is youi probably want to pump a few of your selects
from views into temporary tables (either variable based (if the number of
rows is minimal) or local (if you will have to do any indexing.)  Then just
replace the calls to the view with calls to the temporary table.

--
----------------------------------------------------------------------------
-----------

Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266


Quote:> Hi,
>  I have a master view based on about 30 child views. When
> i run the SQL script, my view is created fine without any
> errors.

> When i try to SELECT from my master view, i am getting the
> following Error:
> ////////////ERROR////////////////////
> Could not allocate ancillary table for view resolution.
> The maximum number of tables in a query (256) was
> exceeded. (State:37000, Native Code: 113E)
> //////END ERROR/////////

> Can anyone help me out...

> Thanks,
> Vasu