status notification in long running sp

I would create a SP_STATUS_INFO table (call it whatever you want).

In the table I would have columns like:

SP_NAME varchar(255)
START_DATE_TIME datetime
END_DATE_TIME datetime NULL
NUM_RECS_PROCESSED int

I think from the column defs you see where I am going. I would probably also write a few SPs that your main, working SPs call in order to "set" the status information:

spStatusInfoStart @SP_NAME, @START_DATE_TIME
spStatusInfoUpdate @SP_NAME, @NUM_RECS_PROCESSED
spStatusInfoEnd @SP_NAME, @END_DATE_TIME

Then, at the beginning of your big worker SP, call spStatusInfoStart passing in the spName and GetDate(), and in your loop, periodically call spStatusInfoUpdate to update the number of recs processed, and finally, at the end of the SP, call spStatusInfo end, passing spName and GetDate() again.

Do all this, and you and easily query the Status Info from your application, directly from the table if you know the spName.

Oh, and of course, this assumes that your app is running the main SP (that takes an hour or so), asynchronously (in another thread or somesuch).

Good luck.

TJR

Thank you very much for this hint! This is a very goog idea.
I´m already calling my SP in a separate thread, so this should work fine.

Oh i forgot, this solution is good but there´s another problem:
I have a transaction around my worker sp, so if i write the table with step-values, i´m not able to read them until my transaction is commited. It´s not feasible for me to commit each cursor step.
Do you have a workaround for this?

Nope...I don't. I am surprised that having a SP that runs that long as one big transaction isn't causing a lot of other issues for you, like transact log space exhaustion, or locking, etc.

Do you really NEED the transaction?

TJR

How about reading step-values with NOLOCK hint?

For this purpose it is very likely harmless.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)