asked on

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

jdlambert1

Which brand of database?

mostym

ASKER

SQL Server 2000

ASKER CERTIFIED SOLUTION

jdlambert1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mostym

ASKER

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

mostym

ASKER

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

mostym

ASKER

can i select from more then 256 tables..?

jdlambert1

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.

jdlambert1

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.

mostym

ASKER

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

jdlambert1

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.

mostym

ASKER

does an alias considerd as a separate table..?
i counted them but they come out be 42 only
not more then 216

jdlambert1

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.

mostym

ASKER

hmnn that can change the count for my views as well.
i am still not sure it exceeds 256 but let me check
thanks

SjoerdVerweij

> joining it to itself with different aliases 260 times <

*gasp*

jdlambert1

Wasn't the fastest query I've ever run.

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

:)

mostym

ASKER

Thanks Guys i think yeah i went over the limit...not good