Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor. Server: Msg 8180, Level 16 ,Server: Msg 102, Level 15, Server: Msg 1018, Level 15

Server: Msg 8180, Level 16

Server: Msg 102, Level 15

Server: Msg 1018, Level 15, State 1, Procedure SendReview, Line 12

Incorrect syntax near ‘FASTFIRSTROW’. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor.

 

We were getting this error from the SQL server job that was running a store procedure inside which we were calling a table that was on a linked server running SQL Server 2005, while the major database was running on SQL Server 2000. We had the linked server setup and it was running fine. We could run queries to this table on the 2005 server without any difficulties.

One thing that I came to find out was the changes in the cursor. Please find details from the following link http://msdn.microsoft.com/en-us/library/ms143359.aspx.

 

Solution:

            1: Either you can have a temp table created out side the cursor to get all the values from across the linked server and then create the cursor over this table.

2: Secondly you can replace the Cursor with a simple loop.

3: Thirdly if possible and you can simply create the Cursor as “FOR Read Only”.

 

Hope this helps you people to migrate your databases happilyJ.

Well I found the solution from this URL:
http://www.eggheadcafe.com/software/aspnet/31278881/cannot-declare-a-cursor-t.aspx

Thought I should share it with everyone.