?
Solved

Total number of tables in a view

Posted on 2004-09-21
16
Medium Priority
?
1,829 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 400 total points
ID: 12118565
SQL Server supports up to 256 tables per select statment/view. Please post your view definitions.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

762 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