Microsoft KB Archive/197392

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

Article ID: 197392

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



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. </li></ul>

<div class="status_section">

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

<div class="moreinformation_section">

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: kbbug kbpending KB197392

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.