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

WORKAROUND

• 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.