May 16, 2008 at 5:47 am
Hello everyone! I need a little help with a query I am trying to get to work... I am writing a an application in visual studio 2005. I have some attributes to be retrieved , these attributes are selected from different Views ( 5 Views ) and each view represents a union of tables from different 23 databases :
As as example
Create View View_1
AS
Select * From DB1..Table1
UNION
Select * From DB2..Table1
.
.
.
Select * From DB23..Table1
I have 5 views created in this way , so any join between these 5 views will generate the following error :
Server: Msg 4414, Level 16, State 1, Line 1
Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
Is there any solution , knowing that i can't change the structure of my databases or views ??
I appreciate your help.
Nadia
May 16, 2008 at 6:32 am
[font="Verdana"]The error is self explanory. The table count in you query is exceeded. Why and what kind of data you need in a single view from more than 260 tables?
Explain your stuff. This is not a good idea to write a view in such a way.
Mahesh[/font]
MH-09-AM-8694
May 16, 2008 at 7:09 am
Thanks for your reply, I know it is not logical to create such view . Actually databases we created in inconsistent way . There supposed to be centralized database that will include all data from other databases but this is not the situation here. So i was stucked in such situation .
May 16, 2008 at 8:38 am
The only thing I can think of doing in this case would be a proc that inserts the desired data, one table at a time, into a temp table. Since I gather you can't do that in this case (it would require writing new code), I don't know what you can do here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply