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

ID: Q195491

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2

SYMPTOMS

The following run-time errors may occur when attempting to Update/Delete data with an ActiveX Data Objects (ADO) Server side cursor:

Cursor operation conflict.

-or-

Errors occurred.

The MSDASQL provider generates the former error message and the SQLOLEDB provider generates the latter.

NOTE: Although the error message occurs, the Update/Delete succeeds.

CAUSE

The SQLServer is using the following configuration options:

sp_configure "user options", 512 SET NOCOUNT ON

RESOLUTION

Change the cursor location to adUseClient or trap the run-time error in Visual Basic. Since 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 "useroptions", 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, and by default, @@ROWCOUNT is reset by any statement not returning row count 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.

A SQL Trace indicates that <sp_cursor> is called for a Server side cursor and a SQL <Update> statement (QBU) for the Client side cursor.

Steps to Reproduce 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 and run the following commands: sp_configure 'user options',512 GO RECONFIGURE GO SET NOCOUNT ON GO

Create a new Visual Basic Project, and add a reference to the Microsoft ActiveX Data Objects 2.0 Library.

Paste the following code into the Form_Load section:


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=sa;Pwd="

      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

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

Q195225 PRB: DAO Runtime Error 3146 When Modifying SQLServer Data

Transact - SQL Reference Help, search on: "sp_configure"

Transact - SQL Reference Help, search on: "SET"

Additional query words:

Keywords : kbADO150 kbADO200 kbDatabase kbODBC kbSQLServ kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:1.5,2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbprb