Updating Data in SQL Server Cursors

OLE DB and SQL Server

OLE DB and SQL Server

Updating Data in SQL Server Cursors

When fetching and updating data through Microsoft® SQL Server™ 2000 cursors, a SQLOLEDB consumer application is bound by the same considerations and constraints that apply to any other client application.

Only rows in SQL Server 2000 cursors participate in concurrent data-access control. When the consumer requests a modifiable rowset, the concurrency control is controlled by DBPROP_LOCKMODE. To alter the level of concurrent access control, the consumer sets the DBPROP_LOCKMODE property prior to opening the rowset.

Transaction isolation levels can cause significant lags in row positioning if client application design allows transactions to remain open for long periods of time. By default, SQLOLEDB uses the read-committed isolation level specified by DBPROPVAL_TI_READCOMMITTED. SQLOLEDB supports dirty read isolation when the rowset concurrency is read-only. Therefore, the consumer can request a higher level of isolation in a modifiable rowset but cannot request any lower level successfully.

Immediate and Delayed Update Modes

In immediate update mode, each call to IRowsetChange::SetData results in a round trip to the SQL Server 2000. If the consumer makes multiple changes to a single row, it is more efficient to submit all changes with a single SetData call.

In delayed update mode, a roundtrip is made to the SQL Server 2000 for each row indicated in the cRows and rghRows parameters of IRowsetUpdate::Update.

In either mode, a round trip represents a distinct transaction when no transaction object is open for the rowset.

When using IRowsetUpdate::Update, SQLOLEDB attempts to process each indicated row. An error occurring due to invalid data, length, or status values for any row does not stop SQLOLEDB processing. All or none of the other rows participating in the update may be modified. The consumer must check the returned prgRowStatus array to determine failure for any specific row when SQLOLEDB returns DB_S_ERRORSOCCURED.

A consumer should not assume that rows are processed in any specific order. If a consumer requires ordered processing of data modification over more than a single row, then the consumer should establish that order in the application logic and open a transaction to enclose the process.