error using cursor

Hi,
I create a sp using the cursor, but I got"
"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."
How can I use cursor? Thanks
Declare myCursor Cursor For
select id, date1
from linkedserver1.import.user.table1
Open myCursor
Fetch Next From myCursor
Into @.ID, @.date1
While @.@.FETCH_STATUS = 0
Begin
Set @.CommandText = 'Delete From temp_table Where' +
' id = ' + Cast(@.ID as varchar(100)) +
' and dt < ''' + Convert(varchar(50),
@.date1, 121) + ''''
Print @.CommandText
-- Exec (@.CommandText)
Fetch Next From myCursor
Into @.ID, @.date1
End
Close myCursor
Deallocate myCursor
Return 1Jen (anonymous@.discussions.microsoft.com) writes:
> I create a sp using the cursor, but I got"
> "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."
> How can I use cursor? Thanks
It is all in the error message. You have an updatable cursor on a linked
server. This requires a higher transaction isolation level than the
standard READ COMMITTED. You can change this with the command
SET TRANSACTION ISOLATION LEVEL.
Now, if the cursor code you have is authentic, there are alternatives.
You are not really using the updatability of the cursor, so you could
add FOR READ ONLY at the end of the cursor declaration.
I am also allergic against keyset-driven cursors. They often causes
nasty surprises. Add INSENSITIVE before the CURSOR keyword, and SQL
Server will copy the data into a temp table and work from there.
Finally:
> Set @.CommandText => 'Delete From temp_table Where' +
> ' id = ' + Cast(@.ID as varchar(100)) +
> ' and dt < ''' + Convert(varchar(50),
> @.date1, 121) + ''''
> Print @.CommandText
> -- Exec (@.CommandText)
I can't see any reason to use dynamic SQL here. Just run the delete
statement.
Or maybe even better: don't use a cursor at all, but just say:
DELETE temp_table
FROM temp_table t
WHERE EXISTS (SELECT * linkedserver1.import.user.table1 l
WHERE t.i = cast(l.id as varchar(100)
AND dt.date < convert(varchar(29), l.date, 126)
(Not also the change of format code. 126 works with all dateformat
settings; 121 does not.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Thanks.
I am not quite familiar with cursor. I just use thebasic
syntax as the BOL example. why the default to be ketset,
updateable?
From BOL declare cursor, mine matches both sql92 and T-SQL
syntax, I don't have any option wors specified, should it
be read-only?
>--Original Message--
>Jen (anonymous@.discussions.microsoft.com) writes:
>> I create a sp using the cursor, but I got"
>> "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."
>> How can I use cursor? Thanks
>It is all in the error message. You have an updatable
cursor on a linked
>server. This requires a higher transaction isolation
level than the
>standard READ COMMITTED. You can change this with the
command
>SET TRANSACTION ISOLATION LEVEL.
>Now, if the cursor code you have is authentic, there are
alternatives.
>You are not really using the updatability of the cursor,
so you could
>add FOR READ ONLY at the end of the cursor declaration.
>I am also allergic against keyset-driven cursors. They
often causes
>nasty surprises. Add INSENSITIVE before the CURSOR
keyword, and SQL
>Server will copy the data into a temp table and work from
there.
>Finally:
>> Set @.CommandText =>> 'Delete From temp_table Where' +
>> ' id = ' + Cast(@.ID as varchar(100)) +
>> ' and dt < ''' + Convert(varchar(50),
>> @.date1, 121) + ''''
>> Print @.CommandText
>> -- Exec (@.CommandText)
>I can't see any reason to use dynamic SQL here. Just run
the delete
>statement.
>Or maybe even better: don't use a cursor at all, but just
say:
> DELETE temp_table
> FROM temp_table t
> WHERE EXISTS (SELECT *
linkedserver1.import.user.table1 l
> WHERE t.i = cast(l.id as varchar(100)
> AND dt.date < convert(varchar(29),
l.date, 126)
>(Not also the change of format code. 126 works with all
dateformat
>settings; 121 does not.)
>--
>Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/book
s.asp
>.
>|||Jen (anonymous@.discussions.microsoft.com) writes:
> I am not quite familiar with cursor. I just use thebasic
> syntax as the BOL example. why the default to be ketset,
> updateable?
Good question. Had it been to me, the default would have been read-only
and insensitive. Updatable is bad, because in practice you very rarely
use this feature. I might update data in the table I'm iterating over,
but in such case I use keys and not WHERE CURRENT OF. And keyset is bad,
because sometimes due to the rules of keyset cursors, SQL Server might
need to resort to a real ugly query plan. (At least this happened in
SQL 6.5. I might not have seen in in SQL 2000, but I try to remember to
always use insensitive cursors.)
> From BOL declare cursor, mine matches both sql92 and T-SQL
> syntax, I don't have any option wors specified, should it
> be read-only?
I believe both INSENSITIVE and FOR READ ONLY conforms with SQL-92.
And, yes, using these are probably your second-best solution.
Your best solution is not use cursors at all, but use a set based
solution.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp