Solved

Total number of tables in a view

Posted on 2004-09-21
16
1,826 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 100 total points
ID: 12118565
SQL Server supports up to 256 tables per select statment/view. Please post your view definitions.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

724 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