RE: [SQLDownUnder] SProc Recursion and A cursor with the name 'eventCursor' already exists error.

[Mitch Denny]

I'd almost be tempted to push this to the application layer, depending
on how much data each event needs to read in - that would allow for a
more flexible solution I think. If you did it at the application layer
you'd probably do some kind of queuing too.







Mitch Denny
web:    http://www.readify.net
email:  mitch.denny@xxxxxxxxxxx
mobile: +61 (414) 610-141
blog:   http://notgartner.com
skype:  callto://mitchdenny

 
-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Martin
Granell
Sent: Friday, 4 March 2005 1:35 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] SProc Recursion and A cursor with the name
'eventCursor' already exists error.

One pattern to solve this is to queue all the events calls, and have the
top level cursor iterating through the queue. As stored procedures get
called, they can add to the queue, and the top level cursor will execute
the queued stored proc at the end of the currently executing set.

 

Note that this does change the order of execution of stored procedures
though (it moves the nested proc calls to the end of the non nested
calls), and so might not be appropriate for your use.

            

In SQL, do this by using a temporary table on the connection.

Check for its existence in the usp_ProcessStatusChange, if it exists,
just add the new events to it, if not, create it, populate it and start
iterating. Make sure that the ordering of the iteration guarantees that
new items get added at the end (e.g. identity column)

 

I'd be interested what other people think of that approach, pros and
cons.

 

Yours,

 

Martin Granell
Senior Consultant
Readify

 

________________________________

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 



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