Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1839
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

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.

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