| title | Update data in cursors (Native Client OLE DB provider) | ||||||
|---|---|---|---|---|---|---|---|
| description | Updating Data in SQL Server Cursors in SQL Server Native Client | ||||||
| author | markingmyname | ||||||
| ms.author | maghan | ||||||
| ms.date | 03/14/2017 | ||||||
| ms.service | sql | ||||||
| ms.subservice | native-client | ||||||
| ms.topic | reference | ||||||
| helpviewer_keywords |
|
[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW]
When fetching and updating data through [!INCLUDEssNoVersion] cursors, a [!INCLUDEssNoVersion] Native Client OLE DB provider consumer application is bound by the same considerations and constraints that apply to any other client application.
Only rows in [!INCLUDEssNoVersion] cursors participate in concurrent data-access control. When the consumer requests a modifiable rowset, the concurrency control is controlled by DBPROP_LOCKMODE. To modify the level of concurrent access control, the consumer sets the DBPROP_LOCKMODE property before opening the rowset.
Transaction isolation levels can cause significant lags in row positioning if client application design lets transactions remain open for long periods of time. By default, the [!INCLUDEssNoVersion] Native Client OLE DB provider uses the read-committed isolation level specified by DBPROPVAL_TI_READCOMMITTED. The [!INCLUDEssNoVersion] Native Client OLE DB provider 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.
In immediate update mode, each call to IRowsetChange::SetData causes a round trip to the [!INCLUDEssNoVersion]. 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 [!INCLUDEssNoVersion] for each row indicated in the cRows and rghRows parameters of IRowsetUpdate::Update.
In either mode, a roundtrip represents a distinct transaction when no transaction object is open for the rowset.
When you are using IRowsetUpdate::Update, the [!INCLUDEssNoVersion] Native Client OLE DB provider tries to process each indicated row. An error occurring because of invalid data, length, or status values for any row does not stop [!INCLUDEssNoVersion] Native Client OLE DB provider processing. All or none of the other rows participating in the update may be modified. The consumer must examine the returned prgRowStatus array to determine failure for any specific row when the [!INCLUDEssNoVersion] Native Client OLE DB provider returns DB_S_ERRORSOCCURRED.
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, the consumer should establish that order in the application logic and open a transaction to enclose the process.