Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1845
  • Last Modified:

Total number of tables in a view

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
mostym
Asked:
mostym
  • 8
  • 7
1 Solution
 
jdlambert1Commented:
Which brand of database?
0
 
mostymAuthor Commented:
SQL Server 2000
0
 
jdlambert1Commented:
SQL Server supports up to 256 tables per select statment/view. Please post your view definitions.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
mostymAuthor Commented:
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
 
mostymAuthor Commented:
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
 
mostymAuthor Commented:
can i select from more then 256 tables..?
0
 
jdlambert1Commented:
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
 
jdlambert1Commented:
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
 
mostymAuthor Commented:
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
 
jdlambert1Commented:
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
 
mostymAuthor Commented:
does an alias considerd as a separate table..?
i counted them but they come out be 42 only
not more then 216
0
 
jdlambert1Commented:
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
 
mostymAuthor Commented:
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
 
SjoerdVerweijCommented:
> joining it to itself with different aliases 260 times <

*gasp*
0
 
jdlambert1Commented:
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
 
mostymAuthor Commented:
Thanks Guys i think yeah i went over the limit...not good
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now