Distributed Queries

Accessing and Changing Relational Data

Accessing and Changing Relational Data

Distributed Queries

Distributed queries access data from multiple heterogeneous data sources, which can be stored on either the same or different computers. Microsoft® SQL Server™ 2000 supports distributed queries by using OLE DB, the Microsoft specification of an application programming interface (API) for universal data access.

This Distributed Queries section discusses general distributed query concepts and describes how to use Transact-SQL statements in distributed queries to access data on separate instances of SQL Server, or non-SQL Server data sources. Other sections of SQL Server Books Online contain additional related information.

For this information See
Configuring OLE DB providers and linked servers Configuring Linked Servers

Configuring OLE DB Providers for Distributed Queries

Distributed queries in distributed transactions Distributed Queries and Distributed Transactions
Linked servers Accessing External Data
ODBC and distributed queries Schema Functions Supporting Distributed Queries
Information about specific OLE DB Providers OLE DB Providers Tested with SQL Server
Performance issues Optimizing Distributed Queries
Reference for OLE DB Provider developers OLE DB Provider Reference for Distributed Queries
Transact-SQL language in distributed queries External Data and Transact-SQL

System Stored Procedures

Troubleshooting distributed queries Distributed Queries Error Messages

Distributed queries provide SQL Server users with access to:

  • Distributed data stored in multiple instances of SQL Server.

  • Heterogeneous data stored in various relational and non-relational data sources accessed using an OLE DB provider.

OLE DB providers expose data in tabular objects called rowsets. SQL Server 2000 allows rowsets from OLE DB providers to be referenced in Transact-SQL statements as if they were a SQL Server table.

Tables and views in external data sources can be referenced directly in SELECT, INSERT, UPDATE, and DELETE Transact-SQL statements. Because distributed queries use OLE DB as the underlying interface, distributed queries can access traditional relational DBMS systems with SQL query processors, as well as data managed by data sources of varying capabilities and sophistication. As long as the software owning the data exposes it in a tabular rowset through an OLE DB provider, the data can be used in distributed queries.

Note  Using distributed queries in SQL Server is similar to the linked table functionality through ODBC, which was supported previously by Microsoft Access. This functionality is now built into SQL Server with OLE DB as the interface to external data.


You are a regional sales manager for a large insurance company that has subsidiaries in several countries. Each regional office selects the product that stores its sales data. The United Kingdom subsidiary stores its data in Oracle; the Australian subsidiary stores its data in Access; the Spanish subsidiary stores data in Microsoft Excel; and the United States subsidiary stores its data in SQL Server. You want a report that lists, on a quarterly basis for the last three years, the insurance policies, the subsidiaries, and the sales representatives with the highest quarterly sales figures. Each of these queries can be accomplished by using a single distributed query, running on SQL Server.

See Also

Configuring Linked Servers

Distributed Queries and Distributed Transactions

Security Account Delegation