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

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.

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

MORE INFORMATION

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 "Implicit Cursor Conversions" topic in Microsoft SQL Server Books Online.