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
Medium Priority
Last Modified: 2007-12-19
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.

Tim Miltz

Question by:tfmiltz
1 Comment
LVL 13

Accepted Solution

danblake earned 1000 total points
ID: 10666269
It all becomes a lot lower:
Tables per SELECT statement 256...

So you can only reference a maximum of 256 different objects in a single query. (from linked servers/views/tables)

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question