FIX: Recordset.Update Causes Error Message: "Cursor operation conflict" to Occur When You Use Server Side Cursors and an INSTEAD OF Trigger

Symptoms

When an ActiveX Data Objects (ADO) application that uses server-side cursors (adUseServer) executes a Recordset.Update on a table with an INSTEAD OF trigger, you receive the following error message:


Run-time error -2147217885 (80040e23) 'Cursor operation conflict'
You do not receive the error message when the table has UPDATE, INSERT or DELETE AFTER triggers.

Cause

The SQL Server 2000 cursor update behavior for INSTEAD OF triggers is incorrect.

Resolution

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, submit a request to Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site: Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.
The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

Date Time Version Size File name
-----------------------------------------------------------------------

12/03/2001 12:14 AM 1,652 bytes EULA.txt
07/02/2002 11:59 AM 5,826 bytes Readme.txt
07/01/2002 4:07 PM 10,794 bytes sp2_qfe_serv_uni.sql
07/01/2002 5:50 PM 12,624,896 bytes Sqlservr.pdb
07/01/2002 5:50 PM 2000.80.652.0 12,624,896 bytes Sqlservr.exe

Workaround

To work around this behavior you can either:


  • Use ADO client-side cursors (adUseClient). -or-

  • Perform the update by using an ADODB.Command object with an UPDATE SQL statement. For example:
    cmd.CommandText = "UPDATE MyTestTable set namefield='asdasdf' where [id] = 1"
    cmd.Execute

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

More Information

Steps to Reproduce the Behavior

  1. Use Query Analyzer to open a connection to the SQL Server 2000 pubs database, and then run the following Transact-SQL statement:

    USE Pubs
    GO
    Create Table TestTable ([ID] int NOT NULL, [NameField] varchar(50) NULL, RowGUID uniqueidentifier Default(NewID()))
    GO
    Insert Into TestTable ([ID], [NameField]) Values (1, 'test1')
    GO
    Insert Into TestTable ([ID], [NameField]) Values (2, 'test2')
    GO
    Insert Into TestTable ([ID], [NameField]) Values (3, 'test3')
    GO
    Create Trigger t_Test On TestTable Instead Of Update
    As
    WAITFOR DELAY '000:00:05'
    GO

  2. Create a Microsoft Visual Basic 6.0 Standard EXE and set a reference to ADO 2.6 (or ADO 2.5 or ADO 2.7).
  3. Put a command button, named Command1, on a form, and then add the following code to the form:
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL As String

    Private Sub Command1_Click()

    conn.Open "Provider=SQLOLEDB;User ID=MyID;Initial Catalog=pubs;Data
    Source=MyServer;Password=MyPassword"
    rs.ActiveConnection = conn
    rs.CursorLocation = adUseServer
    rs.CursorType = adOpenStatic
    rs.LockType = adLockOptimistic
    strSQL = "Select * From TestTable Where [ID] = 1"
    rs.Open strSQL
    rs("NameField") = "incorrectValue"

    rs.Update ' Error 'Cursor operation conflict' occurs here.

    rs.Close
    conn.Close

    End Sub


    The error message shown in the "Symptoms" section occurs when rs.Update executes.

Propriedades

ID do Artigo: 324900 - Última Revisão: 5 de ago de 2008 - Revisão: 1

Comentários