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



The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)

This article was previously published under Q197392
BUG #: 35132 (SQLBUG_70)
BUG #: 35132 (SHILOH_BUG)

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.
The cursor may also fail at declaration time if the FOR UPDATE clause is used.

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 that this is a problem in the Microsoft products that are listed at the beginning of this article.

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.

Modification Type:MinorLast Reviewed:3/14/2005
Keywords:kbBug kbpending KB197392