PRB: ADO Run-Time Error with Update/Delete and SET NOCOUNT ON This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
This article was previously published under Q195491 On This PageSYMPTOMS The following run-time errors may occur when you try to
Update/Delete data with an ActiveX Data Objects (ADO) server-side cursor:
Cursor operation conflict. -or-
Errors occurred. Using ADO 2.1 Service Pack 2
(SP2) and earlier versions, the MSDASQL provider generates the former error
message and the SQLOLEDB provider generates the latter. Using ADO 2.5, both
providers generate the "Cursor operation conflict" error. NOTE: Although the error message occurs, the Update/Delete succeeds. CAUSE SQL Server is using the following configuration options: sp_configure "user options", 512 RESOLUTION Change the cursor location to adUseClient or trap the
run-time error in Visual Basic. Because the Update/Delete actually succeeds,
you can ignore the error message. STATUS This behavior is by design. MORE INFORMATION When SET NOCOUNT ON is configured for the server with
<sp_configure "user options", 512> the @@ROWCOUNT value is reset.
The following statement is from the Transact SQL Help regarding SET NOCOUNT: Turns off the message returned at the end of each statement that states how many rows were affected by the statement.The following statement is from the Transact SQL Help regarding @@ROWCOUNT: Any Transact-SQL statement that does not return rows (such as an IF statement) sets @@ROWCOUNT to 0.SET NOCOUNT ON suppresses DONE_IN_PROC messages. The result is an error message generated by the provider that depends on the @@ROWCOUNT value for rows affected. An error message results although the Update/Delete transaction actually succeeds. Steps to Reproduce the BehaviorWarning The following code changes SQL Server global configuration settings and may impact triggers as well as stored procedures and should be executed with caution.
REFERENCES
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
195225 (http://support.microsoft.com/kb/195225/)
PRB: DAO Run-Time Error 3146 When Modifying SQLServer Data
Transact - SQL Reference Help, search on: "sp_configure"
Transact - SQL Reference Help, search on: "SET" APPLIES TO
| Article Translations
|


Back to the top
