Solved

Total number of tables in a view

Posted on 2004-09-21
16
1,807 Views
Last Modified: 2008-01-09
i get an error saying could not allocate ancillary table  for view or function resolution.
the maximum number in a table or query was exceeded.
How can i fix this.
I have 6 tables being referenced to create two seprate views and then these views are used to create another view
0
Comment
Question by:mostym
  • 8
  • 7
16 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12118453
Which brand of database?
0
 

Author Comment

by:mostym
ID: 12118531
SQL Server 2000
0
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 100 total points
ID: 12118565
SQL Server supports up to 256 tables per select statment/view. Please post your view definitions.
0
 

Author Comment

by:mostym
ID: 12118609
i am doing
select cola,colb,colc from viewa
union all
select cola,colb,colc from viewb
union all
select cola,colb,colc from viewc

i am selecting 6 views
the def for these 6 views is
select cola,colb,colc from tblA
0
 

Author Comment

by:mostym
ID: 12118614
when i run the query in the query analyzer it lets me run the query but is not letting me create a view or when i create a view i do return all rows from the final view it gives me that error.
thanks
0
 

Author Comment

by:mostym
ID: 12118633
can i select from more then 256 tables..?
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12118646
I see no problem with what you have described. If you want us to be able to help resolve this, you should post the exact text of each of the views.

The only suggestion I have is to create your select without using views. This could help you troubleshoot the problem, even if you end up using the views later.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12118651
The only way I know of to exceed the 256 table limit is to combine groups of tables into temporary tables, then join the temporary tables.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:mostym
ID: 12118670
actualy my
6 views are selecting from views and the view is selecting agian from more views..
but the total count of view and table is not more then 216 for sure
i have
final view selecting from
6 viewsA  ---->
selecting from viewB---->wchich is selecing from  views --> wchich is selecting from 2 views --> which is selecing from 2 views ..---> which is selecing from a view and a table
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12118692
It's generally not a good idea to use nested views like this. Even if you stay within the limits, it doesn't let SQL Server optimize the query as well as it can if all the conditions are in a single statement.

Again, I suggest eliminating some of these nested levels, for troubleshooting this error, if for no other reason.
0
 

Author Comment

by:mostym
ID: 12118699
does an alias considerd as a separate table..?
i counted them but they come out be 42 only
not more then 216
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12118743
Good question. I wouldn't have thought so, but I just ran a test using only one table, but joining it to itself with different aliases 260 times. The result:

Server: Msg 106, Level 15, State 1, Line 267
Too many table names in the query. The maximum allowable is 256.
0
 

Author Comment

by:mostym
ID: 12118863
hmnn that can change the count for my views as well.
i am still not sure it exceeds 256 but let me check
thanks
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12124451
> joining it to itself with different aliases 260 times <

*gasp*
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12124824
Wasn't the fastest query I've ever run.

I just wrote a little loop to create all the code for the extra joins.

:)
0
 

Author Comment

by:mostym
ID: 12155586
Thanks Guys i think yeah i went over the limit...not good
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now