Keyset-Driven Cursors Requirements for OLE DB Providers
Transact-SQL keyset-driven cursors can reference remote tables only if the following conditions are met:
The distributed query must meet the requirements for SELECT statements used in a DECLARE CURSOR statement that declares the keyset-driven cursor. For more information about the Transact-SQL conditions for keyset-driven cursor support, see DECLARE CURSOR (Transact-SQL).
All local tables in the query must have a unique index. The index of the remote table should be exposed through the INDEXES rowset of the IDBSchemaRowset interface.
Index Requirements on OLE DB Providers
SQL Server can use indexes on tables from an OLE DB provider to evaluate certain queries. For SQL Server to use an index, the provider should expose OLE DB interfaces that allow scanning an index rowset, searching in the index rowset using index column values, and setting the row position in the base table rowset using bookmarks obtained from the index rowset.
Using the indexes of the OLE DB provider has performance benefits only when the index and table rowsets are on the same computer as the instance of SQL Server. Therefore, the Index AS Access Path option should be set only if the data source is on the same computer as SQL Server.
SQL Server can use the indexes of an OLE DB provider only if the following conditions are met:
The provider must support the IDBSchemaRowset interface with the TABLES, COLUMNS, and INDEXES schema rowsets.
The provider must support opening a rowset on an 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.
Rowsets opened against the indexed base table, through IOpenRowset, must support the IRowsetLocate interface for positioning on a row in the base table based off a bookmark obtained from the index rowset.
If the OLE DB provider meets these requirements, the SQL Server administrator can set the Index As Access Path provider option to enable SQL Server to use the indexes of the provider to evaluate the queries. By default, SQL Server does not try to use the indexes of the provider unless this option is set.
Updatable Keyset Cursor Requirements
A remote table can be updated or deleted through a keyset cursor defined on a distributed query. For example: UPDATE | DELETE remote_table WHERE CURRENT OF cursor_name.
The following are the conditions under which updatable cursors against distributed queries are allowed:
The provider should meet the conditions for updates and deletes on the remote table. For more information, see UPDATE and DELETE Requirements for OLE DB Providers.
All the cursor operations must be in an explicit user transaction, or multi-statement transaction, with read-repeatable isolation level or serializable isolation level.
The provider must support distributed transactions by using the ITransactionJoin interface.