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
--