SQL Server Distributed Query - what is the limit to how many different db's can be accessed in one query.
Posted on 2004-03-23
Using a distributed, also called heterogeneous query with SQL Server 2000, what is the maximum number of different databases that can be referenced in a single query? SQL Server 2000 supports 2^16-1 - 32767 databases in a named instance. Again, the question is, in a distributed query, such as :
SELECT s1.income, s2.contractlength from server1.database.owner.table s1, server2.database32.owner.table s2
how many different db's can be referenced in one query, is it indeed up to 32767 ? Or, can you cross query across 5 instances, including 150,000 references to db+table in the query.
Granted, 150,000 databases may seem absurd, however, I am wondering what the maxium is, whether it is 16 different databases, or even 32,767.