Cursor Support

Both snapshot and keyset cursors are supported against distributed queries if the provider supports the necessary OLE DB functionality. Dynamic cursors are not supported against distributed queries. A user request for a dynamic cursor against a distributed query is downgraded to a keyset cursor.

Snapshot cursors are populated at cursor open time and the result set remains unchanged; updates, inserts, and deletes to the underlying tables are not reflected in the cursor.

Keyset cursors are populated at cursor open time and the result set remains unchanged throughout the lifetime of the cursor. However, updates and deletes to underlying tables are visible in the cursor as the rows are visited. Inserts to underlying tables that may affect cursor membership are not visible.

A remote table can be updated or deleted through a cursor that is defined on a distributed query and references the remote table if the provider meets the conditions for updates and deletes on the remote table, for example, table UPDATE | DELETE <remote-table> WHERE CURRENT OF <cursor-name>. For more information, see “UPDATE and DELETE Statements” later in this chapter.

Keyset Cursor Support Requirements

A keyset cursor is supported on a distributed query if all the Transact-SQL syntax requirements are met and either of these exist:

• The OLE DB provider supports reusable bookmarks on all the remote tables in the query. Reusable bookmarks can be consumed from a rowset on a given table and used on a different rowset of the same table. The support for reusable bookmarks is indicated through the TABLES_INFO schema rowset of IDBSchemaRowset by setting the BOOKMARK_DURABILITY column to BMK_DURABILITY_INTRANSACTION or a higher durability.
• All the remote tables expose a unique key through the INDEXES rowset of IDBSchemaRowset interface. There should be an index entry with the UNIQUE column set to VARIANT_TRUE.

Updatable Keyset Cursor Requirements

A remote table can be updated or deleted through a keyset cursor that is 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:

• Updatable cursors are allowed if the provider also meets the conditions for updates and deletes on the remote table. For more information, see “UPDATE and DELETE Statements” later in this chapter.
• All the updatable keyset cursor operations must be in a user-defined transaction with read-repeatable isolation level or a higher isolation level. Furthermore, the provider must support distributed transactions with the ITransactionJoin interface.