Export (0) Print
Expand All

Distributed Queries

SQL Server 2000

  New Information - SQL Server 2000 SP3.

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
Information about specific OLE DB Providers OLE DB Providers Tested with SQL Server
Language support for OLE DB providers SQL Dialect Requirements for OLE DB Providers
Linked servers Accessing External Data
ODBC and distributed queries Schema Functions Supporting Distributed Queries
Performance issues Optimizing Distributed Queries
Reference for OLE DB Provider developers OLE DB Provider Reference for Distributed Queries
Statistics requirements Distribution Statistics Requirements for OLE DB Providers

Programming OLE DB SQL Server Applications

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.

Distributed queries can allow users to access another data source (for example, files, non-relational data sources such as Active Directory™, and so on) using the security context of the Microsoft Windows® account under which the SQL Server service is running. SQL Server 2000 impersonates the login appropriately for Windows NT® logins; however, that is not possible for SQL Server logins. This can potentially allow a distributed query user to access another data source for which they do not have permissions, but the account under which the SQL Server service is running does have permissions. Use the sp_addlinkedserver stored procedure to define the specific logins that are authorized to access the corresponding linked server. This control is not available for ad hoc names, so use caution in enabling an OLE DB provider for ad hoc access.

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.

Example

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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft