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

sp_configure "user options", 512
					

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 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 or SQL Server Query Analyzer and run the following commands: sp_configure 'user options',512 GO RECONFIGURE GO
2. Create a new Visual Basic Project, and add a reference to the Microsoft ActiveX Data Objects 2.x Library.
3. 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
   					

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