Microsoft KB Archive/195491

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

Article ID: 195491

Article Last Modified on 3/2/2005

-

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

-



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.



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  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.  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= and PWD= 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= ;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 </li> Run the Project and the following error message occurs:

Run-time error '-2147217885' [Microsoft][ODBC SQL Server Driver]Cursor operation conflict

</li> Uncomment the CursorType and CursorLocation variables and re-run the project. Note that the error message does not occur when using Client side cursors.</li>  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                   </li></ol>

<div class="references_section">