BUG: Error 16929, "Cursor Is Read Only" for Cursor w/OpenQuery()

• Microsoft SQL Server version 7.0

SYMPTOMS

Updating a remote table using the UPDATE WHERE CURRENT OF statement on a cursor opened with the OpenQuery() syntax fails with the following error message:

Msg 16929, Level 16, State 1
The cursor is READ ONLY.

CAUSE

A static cursor is being generated with the OpenQuery() syntax.

WORKAROUND

To work around this problem, do either of the following:

• Use a keyset (SCROLL) cursor with the 4-part naming convention, rather than with the OpenQuery() syntax. This requires the following:
  
  
  • The remote OLE DB provider must support the ITransactionJoin interface.
  
  
  
  • Microsoft Distributed Transaction Coordinator (DTC) must be running on the local server.
  
  
  
  • The cursor operation must be performed within a transaction with an isolation level of REPEATABLE_READ or SERIALIZABLE.
    
    -or-
  
  
  



• Perform a direct update to the table rather than an update through the cursor. To do this, you can open a cursor to fetch the unique key in the table and perform an UPDATE statement based on the value of this key, as in the following example:
  

  
        declare @i int
        declare curs1 cursor for
        select a from OpenQuery(server1, 'select a from pubs.dbo.ab')
        open curs1
        fetch curs1 into @i
        while @@fetch_status <> -1
        begin
        update openquery(server1, 'select a, b from pubs.dbo.ab')
        set b = 'XXX' where a = @i
        fetch curs1 into @i
        end
        close curs1
        deallocate curs1
        go
   

  Where column 'a' is the primary key for table 'ab' on the linked server 'server1'. Column 'b' may be any other column in the table.
  
  NOTE: With this method you can use either the 4-part naming convention or the OpenQuery() syntax.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

MORE INFORMATION

Dynamic, Forward-Only and Fast Forward-Only cursors are not supported over distributed queries. If you request any of the above types of cursors, the cursor will attempt to open a keyset cursor by default. If a keyset cursor is not supported for the query, a static cursor will be generated. This is expected behavior.

However, if a keyset cursor is opened using the OpenQuery() syntax, it will incorrectly be downgraded to a static cursor. The end result is, no matter what type of cursor is requested with the OpenQuery() syntax, a static cursor is generated. Because static cursors are Read Only, any attempt to update the cursor will receive the error message noted in the SYMPTOMS section of this article.

For more information on cursors, see the "Declare Cursor (T-SQL) topic in the SQL Server 7.0 Books Online.

Additional query words: insensitive err oledb

Keywords : SSrvTran_SQL kbbug7.00
Version : WINNT:7.0
Platform : winnt
Issue type : kbbug