Microsoft KB Archive/269694

= PRB: Keyset Cursor to Access a Remoted Table Through a Linked Server May Cause Error 16953 =

Article ID: 269694

Article Last Modified on 10/28/2000

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q269694



SYMPTOMS
When you declare a KEYSET cursor to access a remote table on a linked server, the cursor might not be created and an error message might occur. The same behavior occurs when Microsoft SQL Server converts other cursor types to KEYSET cursors.

Both of the following cursor declarations cause the 16953 error message to occur: DECLARE TEST_CURSOR CURSOR KEYSET FOR SELECT * FROM LinkedServer1.pubs.dbo.authors

DECLARE TEST_CURSOR CURSOR FOR SELECT * FROM LinkedServer1.pubs.dbo.authors The text of the error message is:

Server: Msg 16953, Level 16, State 1, Line 3

Updatable keyset cursors on remote tables require a transaction with

the REPEATABLE_READ or SERIALIZABLE isolation level.

If you subsequently open the cursor by using open TEST_CURSOR the following error message occurs:

Server: Msg 16916, Level 16, State 1, Line 1

A cursor with the name 'TEST_CURSOR' does not exist.



CAUSE
This behavior is by design.



WORKAROUND
To work around this behavior, use one of the following methods:   Set the transaction Isolation level to REPEATABLE READ or SERIALIZABLE, and then wrap the cursor operations in a transaction. For example, you can use the following code: set transaction isolation level repeatable read Begin tran DECLARE TEST_CURSOR CURSOR KEYSET FOR SELECT * FROM LinkedServer1.pubs.dbo.authors open TEST_CURSOR --do your work

deallocate TEST_CURSOR commit tran set transaction isolation level read committed -or-

  Use a STATIC or INSENSITIVE cursor. The following two cursor declarations work. DECLARE TEST_CURSOR INSENSITIVE CURSOR FOR SELECT * FROM LinkedServer1.pubs.dbo.authors

DECLARE TEST_CURSOR CURSOR STATIC FOR SELECT * FROM LinkedServer1.pubs.dbo.authors 



Steps to Reproduce The Behavior
To reproduce the behavior, run the script that follows.

NOTE: You must change the linked server name, user login, and password to match your environment. Also, the article assumes that the authors table in pubs has not been altered from the original installation and that all the indexes have not been dropped by users since installation. exec sp_dropserver 'LinkedServer1', 'droplogins' go exec sp_addlinkedserver @server = N'LinkedServer1' go exec sp_setnetname @server=N'LinkedServer1', @netname=N'ServerA' go exec sp_addlinkedsrvlogin @rmtsrvname='LinkedServer1', @useself='false', @rmtuser='sa', @rmtpassword='' go

--this is successful exec LinkedServer1.master.dbo.sp_who

-- then try to declare a simple cursor -- The following two cursor declaration will fail DECLARE TEST_CURSOR CURSOR KEYSET FOR SELECT * FROM LinkedServer1.pubs.dbo.authors

open TEST_CURSOR deallocate TEST_CURSOR go --Or this declaration DECLARE TEST_CURSOR CURSOR FOR SELECT * FROM LinkedServer1.pubs.dbo.authors open TEST_CURSOR deallocate TEST_CURSOR go

Implicit Server Cursor Conversations
The 16953 error message can occur even if you do not explicitly declare a keyset cursor. SQL Server tries to implicitly convert FORWARD_ONLY, FAST_FORWARD or DYNAMIC cursors to a KEYSET cursor whenever queries access a remote table in a linked server. This is why one of the preceding cursor declarations, without explicitly specifying KEYSET, still generates the same error message.

In addition, if a remote table does not have a unique index, the cursor is converted to static implicitly. As a result, you might not see the 16953 error message. Therefore, it might seem that there are some inconsistent results when in fact the results are consistent. In a development environment, where performance is not a concern, a table might not have any indexes. The preceding cursor declarations do not generate errors and are implicitly converted. Everything works fine until you create a unique index for the table in production.

For more information about implicit cursor conversions, refer to the &quot;Implicit Cursor Conversions&quot; topic in Microsoft SQL Server Books Online.

Additional query words: Distributed Queries

Keywords: kbprb KB269694

-

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

© Microsoft Corporation. All rights reserved.