PRB: ADO Run-Time Error with Update/Delete and SET NOCOUNT ON

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q195491

SYMPTOMS

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.

Back to the top

CAUSE

SQL Server is using the following configuration options:

sp_configure "user options", 512

Back to the top

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.

Back to the top

STATUS

This behavior is by design.

Back to the top

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.

Back to the top

Steps to Reproduce the Behavior

Warning The following code changes SQL Server global configuration settings and may impact triggers as well as stored procedures and should be executed with caution.

Start ISQL-W or SQL Server Query Analyzer and run the following commands: sp_configure 'user options',512 GO RECONFIGURE GO
Create a new Visual Basic Project, and add a reference to the Microsoft ActiveX Data Objects 2.x Library.

Paste the following code into the Form_Load section:

NoteYou must change UID=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

Dim ADOCn As ADODB.Connection
      Dim ADORs As ADODB.Recordset
      Dim strConnect As String

      strConnect = "Provider=MSDASQL;Driver={SQL
                 Server};Server=(local);Database=Pubs;Uid=<user name>;Pwd=<strong password>"

      Set ADOCn = New ADODB.Connection
      With ADOCn
         .ConnectionString = strConnect
         .CursorLocation = adUseServer
         .Open
      End With

      Set ADORs = New ADODB.Recordset
      With ADORs
         .ActiveConnection = ADOCn
         .CursorLocation = adUseServer 'adUseClient
         .CursorType = adOpenKeyset 'adOpenStatic
         .LockType = adLockOptimistic
         .Open "SELECT * FROM Authors"
      End With

      ADORs.Fields("au_lname").Value = ADORs.Fields("au_lname").Value
      ADORs.Fields("au_fname").Value = ADORs.Fields("au_fname").Value
      ADORs.Update

Run the Project and the following error message occurs:
Run-time error '-2147217885' [Microsoft][ODBC SQL Server Driver]Cursor operation conflict
Uncomment the CursorType and CursorLocation variables and re-run the project. Note that the error message does not occur when using Client side cursors.
Run the following in ISQL_w to restore the global SQL Server user option configuration settings to the original values:
sp_configure 'user options',0 GO RECONFIGURE GO

Back to the top

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"

Back to the top

APPLIES TO

Microsoft ActiveX Data Objects 1.5
Microsoft ActiveX Data Objects 2.0
Microsoft ActiveX Data Objects 2.1 Service Pack 2
Microsoft ActiveX Data Objects 2.5
Microsoft ActiveX Data Objects 2.6
Microsoft ActiveX Data Objects 2.7

Back to the top

kbdatabase kbprb KB195491

Back to the top

Retired KB Content Disclaimer

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.

Back to the top