Hello,
Try adding this before the declare:
IF Cursor_Status('variable', ' EventCursor ') <= 0
Setup
EventCursor
Otherwise just continue…..should
work
Regards,
Michael van der Veeke
Database Administrator
Border Express Pty Ltd
Phone: (02) 6022 6046
Mobile : 0419 419 547
Email : michaelvanderveeke@xxxxxxxxxxxxxxxxxxxx
Web : http://www.borderexpress.com
NOTICE
The information contained in this electronic mail message is privileged and
confidential, and is intended only for use of the addressee. If you are not the
intended recipient, any disclosure, reproduction, distribution or other use of
this communication is strictly prohibited. If you have received this communication
in error, please notify the sender by reply transmission and delete the message
without copying or disclosing it.
From:
SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of David Ames
Sent: Friday, 4 March 2005 12:42
PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: [SQLDownUnder] SProc
Recursion and A cursor with the name 'eventCursor' already exists error.
Hi All,
I'm currently working on a ticking system with a fairly
complex status tracking sub-system.
Essentially, when a status changes, we fire a stored
procedure to handle updates to several tables. (usp_ProcessStatusChange)
Business rules dictate that when a status changes, 0..n
'events' can fire. These events are really just more stored
procedures. We fire them by setting up an events cursor, then EXECing the
event stored procedure.
This system works really well, except I now find myself having
to code an event that itself calls usp_ProcessStatusChange, ie, a
recursive operation. - (I am aware that SQL limits recursion to 16 levels
deep).
Logically it looks something like this
usp_ProcessStatusChange
Setup EventCursor
While Fetch_Status = 0
EXEC
usp_MyNewEvent
LoopEventCursor
CloseDeallocte EventCursor
usp_MyNewEvent
Grab a related job ticket
Do some processing on it
EXEC usp_ProcessStatusChange
My problem is that when usp_MyNewEvent calls usp_ProcessStatusChange
my connection already has a cursor named EventCursor, and thus I receive a A
cursor with the name 'eventCursor' already exists error.
I currently can't see a way around this and I'm hoping one
of the experts on this list can suggest a different approach for this.
Thanks,
Dave
to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
