SQL Server 2000 distributed queries: OLE DB connections

Overview and terminology

In MicrosoftSQL Server 2000, distributed queries allow SQL Server users to access data other than SQL Server based servers (in other servers running SQL Server or in other data sources with OLE DB interfaces). OLE DB provides a unified way to access list data from heterogeneous data sources.

In this article, a distributed query refers to any select, INSERT, UPDATE, or DELETE statement that references a table or rowset in one or more external OLE DB data sources.

A remote table is a table that is stored in an OLE DB data source and is not on the same server as the SQL Server that is executing the query. A distributed query can access one or more remote tables.

OLE DB Provider Category

Depending on the capabilities of the OLE DB provider in distributed queries in SQL Server, we classify them into the following categories. By definition, they are not mutually exclusive; a provider may belong to one or more categories:

SQL command Provider

Indexing provider

Simple table Provider

Non-SQL command provider

SQL command Provider

All providers that support command objects in the SQL standard syntax (SQL Server-approved) belong to this category. The following are the prerequisites for the OLE DB provider to be considered SQL command providers by SQL Server:

The provider must support the Command object and all its mandatory OLE DB interfaces: ICommand, ICommandText, IColumnsInfo, ICommandProperties, and IAccessor.

The SQL syntax supported by the provider must be at least a subset of SQL. The provider must report the syntax through the Dbprop_sqlsupport property.

Examples of SQL command providers are: Microsoft OLE DB Provider forsql Server and Microsoft OLE DB Provider for ODBC.

Indexing provider

The index provider supports and provides an index corresponding to OLE DB, while also allowing the base table to be searched based on the index. The following are the prerequisites for an OLE DB provider being treated as an index provider by SQL Server:

Providers must support the IDBSchemaRowset interface with TABLES, COLUMNS, and INDEXES schema rowsets.

The provider must support opening the rowset in the index through IOpenRowset (by specifying the index name and the corresponding base table name).

The Index object must support all its mandatory interfaces: IRowset, IRowsetIndex, IAccessor, IColumnsInfo, IRowsetInfo, and Iconverttypes.

A rowset opened with an indexed base table (by using IOpenRowset) must support the IRowsetLocate interface so that it can be positioned on a bookmark line.

If an OLE DB provider satisfies the above criteria, the user can set the provider option Index as Access Path to allow SQL Server to use the provider's index to execute the query. By default, SQL Server does not attempt to use the provider's index unless the option is set.

Note: SQL Server supports multiple options that affect the way SQL Server accesses OLE DB providers. You can use the Linked Server Properties dialog box in SQL Server Enterprise Manager to set these options.