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

ID: Q195491

The information in this article applies to:

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 "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, 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.

1. Start ISQL-W and run the following commands: 

      sp_configure 'user options',512
      GO
      RECONFIGURE
      GO
      SET NOCOUNT ON
      GO
2. Create a new Visual Basic Project, and add a reference to the Microsoft 
   ActiveX Data Objects 2.0 Library.
3. 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
4. Run the Project and the following error message occurs: 

      Run-time error '-2147217885'
      [Microsoft][ODBC SQL Server Driver]Cursor operation conflict
5. Uncomment the CursorType and CursorLocation variables and re-run the 
   project. Note that the error message does not occur when using Client
   side cursors.
6. 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: 

   ARTICLE-ID:  Q195225
   TITLE     :  PRB: DAO Runtime Error 3146 When Modifying SQLServer Data
Transact - SQL Reference Help, search on: "sp_configure"

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

(c) Microsoft Corporation 1998. All Rights Reserved. Contributions by Mark S. Miller, Microsoft Corporation.

Additional query words: 

Keywords          : kbADO150 kbADO200 kbODBC kbSQLServ 
Version           : WINDOWS:1.5,2.0
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: December 1, 1998