WorkAround ?? Too many table names in the query

[ajacode]

Here's the full message
Server: Msg 106, Level 15, State 1, Line 1329
Too many table names in the query. The maximum allowable is 256.


Any Ideas on how to work around this limit ??

Thanks

AJA

 

[JamesLean]

That must be one heck of a complex query if it's using more than 256 tables!!

I would suggest you try and break up the joins into separate chunks, encapsulated in views, functions etc, and then join them back together in your select.

I must admit, it's not something I've ever come across before!

--James

 

[ajacode]

Thanks James,

Yeah I have 34 different segments each of which roll up to a single "Table" of 1 row with aliased column heads and probably 25 of them are 3 unions and a sublect with an outerjoin thrown in..

Seems like the dtabse designer merely thought about data input not getting information back out for reporting purposes... but I'm with you I've never run across this and Microsoft has ZILCH for any knowledge base article that I can find..

Thanks Again

AJA

 

[JamesLean]

To be honest, I doubt you'll find any support info on this. You've just hit the system limit for number of tables in a select, it's not really an "error" as such.

As I say, you'll have to rethink your method of getting the data you need - perhaps return it in two "chunks" maybe?

FYI, you can get a list of other system limits from BOL - look up "maximum capacity specifications" in the index.

--James

 

[SQLBill]

You might want to take a look at the FAQs. I have a FAQ about an undocumented stored procedure that lets you run up to three commands against all tables in a database. You might be able to use it with your query.

-SQLBill