A cursor with the name 'MyRS' already exists

Any help appreciated on this one as I have scoured the internet and got no j
oy!
I have a complex set of triggers and stored procedures that should result in
changes to my _Company table being replicated to an equivalent table in a
different database on a different SQL 2000 Server.
This works 99% of the time. However, when I update a particular record, the
trigger associated with that table fires, and then returns error 16915 (A
cursor with the name 'MyRS' already exists).
It appears as though the update tried to fire previously and then some error
occurred, so that it now thinks this record is mid-transaction' I don't
really know.
The main question is, can I trace the root cause of this error message, and
remove it?
As I stated earlier, in 99% of cases, everything works fine, so I think this
is a problem with the cursor being left in an "open" state, rather than a
fundamental issue with the trigger code.
Here is the test code I am using to update the record and hence fire the
trigger:
UPDATE _Company SET update_note_at='Not Synchronised with Sage, Telesales'
WHERE (update_note_at IS NULL) AND reference IN('ABC1234567')
Here is the output received when running the above in query analyser:
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Procedure at_Sage_Cust_Exp, Line 16
A cursor with the name 'MyRS' already exists.
The statement has been terminated.
Here is the actual code of the trigger:
CREATE TRIGGER at_Sage_Cust_Exp ON dbo._Company
FOR UPDATE
NOT FOR REPLICATION
AS
DECLARE @.TrigDate AS datetime
SET @.TrigDate=getdate()
DECLARE MyRS CURSOR
FOR
SELECT id, update_at, update_sage, On_AT, On_Sage, update_note_at,reference
FROM inserted
OPEN MyRS
declare @.coId as uniqueidentifier
DECLARE @.update_at AS bit
DECLARE @.Update_Sage AS bit
DECLARE @.On_AT AS bit
DECLARE @.On_Sage AS bit
DECLARE @.update_note_at AS nvarchar(100)
DECLARE @.reference AS nvarchar(30)
DECLARE @.Old_update_note_at AS nvarchar(100)
FETCH NEXT FROM MyRS INTO @.coId, @.update_at, @.Update_Sage, @.On_AT, @.On_Sage,
@.update_note_at, @.Reference
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SET @.Old_update_note_at=(SELECT update_note_at FROM deleted WHERE id=@.coId)
IF @.update_at = 1 OR @.On_AT = 1
begin
set nocount on
IF @.update_note_at<>'Sage instigated sched_date update'
exec ap_Sage_CustAT_Exp @.coId
ELSE
UPDATE _Company SET update_note_at=@.Old_update_note_at WHERE id=@.coId
set nocount off
end
IF @.Update_Sage = 1 OR @.On_Sage = 1
begin
set nocount on
IF @.update_note_at<>'Sage instigated sched_date update'
exec ap_Sage_Cust_Exp @.coId, @.Reference
ELSE
UPDATE _Company SET update_note_at=@.Old_update_note_at WHERE id=@.coId
set nocount off
end
FETCH NEXT FROM MyRS INTO @.coId, @.update_at, @.update_sage, @.On_AT,
@.On_Sage, @.update_note_at,@.Reference
END
CLOSE MyRS
DEALLOCATE MyRS
THANKS,
Andy, MCDBAChange your cursor to "local"
that is
DECLARE MyRS CURSOR LOCAL
FOR
SELECT id, update_at, update_sage, On_AT, On_Sage,
update_note_at,reference
FROM inserted|||Never use cursors in triggers, is my advice. Why would you want to turn
every set-based update into a cursor?
Most of what you have can be done with two UPDATE statements so the
cursor looks superfluous. The only question is what your two SPs do.
Change them to set-based logic and you won't need the cursor at all.
David Portas
SQL Server MVP
--

A cursor with the name deadrow already exists.

I'm trying to track this error from the driver down:
'42000' [-1] '[Microsoft][ODBC SQL Server Driver][SQL Server]A cursor
with the name 'deadrow' already exists. ** AND ** [Microsoft][ODBC SQL
Server Driver][SQL Server]The statement has been terminated.'
[msg='SQL call failed.']."Todd" <talltodd@.hotmail.com> wrote in message
news:eda42e3f.0401090715.5e75fd6f@.posting.google.c om...
> I'm trying to track this error from the driver down:
> '42000' [-1] '[Microsoft][ODBC SQL Server Driver][SQL Server]A cursor
> with the name 'deadrow' already exists. ** AND ** [Microsoft][ODBC SQL
> Server Driver][SQL Server]The statement has been terminated.'
> [msg='SQL call failed.'].

It's not possible to say exactly what happened without more information, but
it seems that your client application has declared a cursor called deadrow
but not closed and deallocated it afterwards. If the same code is then run
again, you will get this error.

Simon|||To add to Simon's response, it's a good practice to explicitly declare
cursors as LOCAL unless you have a reason to do otherwise. This will ensure
the cursor is deallocated when it goes out of scope.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Todd" <talltodd@.hotmail.com> wrote in message
news:eda42e3f.0401090715.5e75fd6f@.posting.google.c om...
> I'm trying to track this error from the driver down:
> '42000' [-1] '[Microsoft][ODBC SQL Server Driver][SQL Server]A cursor
> with the name 'deadrow' already exists. ** AND ** [Microsoft][ODBC SQL
> Server Driver][SQL Server]The statement has been terminated.'
> [msg='SQL call failed.'].|||Dan Guzman (danguzman@.nospam-earthlink.net) writes:
> To add to Simon's response, it's a good practice to explicitly declare
> cursors as LOCAL unless you have a reason to do otherwise. This will
> ensure the cursor is deallocated when it goes out of scope.

Yeah, but then you cannot declare them as INSENSITIVE, which also is a
good practice, as that saves you from the surprises that keyset-driven
cursors can give you.

Maybe STATIC or FAST_FORWARD are the same as INSENSITIVE, but I have
never managed to grasp the cursor excesses that were added to SQL7.

Besides, INSENSITIVE is ANSI compliant, while LOCAL is not.

There is a database option to make local cursors the default, though.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Actually we don't have a cursor named 'deadrow' it seems so we figure it
coming from the odbc driver for sql server.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!