OpenAccess™ and SQL Server 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 OpenAccess in distributed queries to access data on an instance of SQL Server and non-SQL Server data sources.

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 OpenAccess OLE DB provider.

OpenAccess OLE DB provider expose data in tabular objects called rowsets. SQL Server 2000 allows rowsets from OpenAccess OLE DB provider 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.

Using Client/Server OpenAccess OLEDB Provider to interact with SQL Server as shown in figure.

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.

Please refer to our OLEDB FAQ section for configuring SQL Server 7.0 Linked Server.

Click here for Technical Support Information.