[RESOLVED] How can I tell if a SPROC is running?

[Mark Gambo]

How can I tell if a SPROC is running on a MS SQL Server 7, via code?

[szlamany]

I'm not sure you can...

Even in 2000 or 2005...

I know the profiler will show that - but you certainly don't want to run the profiler for that reason.

SP_WHO or SP_WHO2 (MS SQL 2000) show the last SQL command executed - but that's the SQL in the SPROC - not the SPROC itself.

Do you want to track all SPROCS?

[Mark Gambo]

I'm not sure you can...

Even in 2000 or 2005...

I know the profiler will show that - but you certainly don't want to run the profiler for that reason.

SP_WHO or SP_WHO2 (MS SQL 2000) show the last SQL command executed - but that's the SQL in the SPROC - not the SPROC itself.

Do you want to track all SPROCS?

No, I want to track this one only. Reason being it is updating quite a few records and I don't want multiple users to run it at the same time. Oh well, it isn't a big problem if the users run it at the same time. Thanks!

[szlamany]

It wouldn't be a hard thing to INSERT a row in a tracking table when the SPROC starts and DELETE said row right as it's about to complete...

That way you could check to see if the row exists and exit with a message to the user saying that it's already running...

[si_the_geek]

Well one solution (which I think Steve may have been thinking of) is to create a 'currently in use' table (possibly temporary), which lists the people who are using which SP (data to be written/updated by the SP's themselves).

Given the extra details, I can see that a single field/record may well be enough, and that a temporary table would suffice.

At the start of the SP you would check if the temp table exists - if it does then exit the SP (with a message to the user), otherwise create/fill it. At the end of the SP you would simply drop the temp table.


Of course I would expect that Steve has other ideas too, or at least clarification of possible issues with this method!


edit: how long did I take to write that!

[Mark Gambo]

I was thinking of something similiar but the problems begin if one of the user's apps hang and the table isn't deleted. Thanks Si.

[Mark Gambo]

I guess even if the user's app hangs, the SPROC will continue to run unless the box the SQL Server is running on hangs. I think I will try it. Thanks guys, reps all around!!!

[szlamany]

Si - I was basically thinking along those lines (briefly through )

...

@Mark - a row inserted into a table at the top of the sproc will auto-ROLLBACK if the SPROC never completes (due to lost connection whatever). I'm not sure what you mean by a SPROC continuing to run if the connection is lost. A SPROC is an "implied transaction" - so it either all completes or it all rolls back...

[si_the_geek]

Nice, I'm on the right lines then!

After reading thinking about it some more, I dont think the table should be temporary (a proper table would work faster, and allow easier correction if there are problems).


I think Mark's concern is with the connection from VB (or whatever) to the DB being lost, and the SP just "hanging". I'm pretty sure this wouldn't happen tho.

[szlamany]

I think Mark's concern is with the connection from VB (or whatever) to the DB being lost, and the SP just "hanging". I'm pretty sure this wouldn't happen tho.

Yeah - I agree...

I've aborted long running rogue SPROCS by TASK MANAGER'ing QA and they rollback - from what I can remember...

They might beat up the log file on the rollback - I believe I've even corrupted one MS SQL DB doing this (I'm blocking that memory)...

[Mark Gambo]

@Mark - a row inserted into a table at the top of the sproc will auto-ROLLBACK if the SPROC never completes (due to lost connection whatever). I'm not sure what you mean by a SPROC continuing to run if the connection is lost. A SPROC is an "implied transaction" - so it either all completes or it all rolls back...

@Steve - I was mistakenly thinking of an ADO connection not realizing that once the VB App starts the SPROC it is on it own regardless if the app hangs. Thanks again!

[szlamany]

...once the VB App starts the SPROC it is on it own regardless if the app hangs.

Mark - I'm not sure that's the case...

The connection being lost prior to the SPROC completing is I believe (and I could be wrong here) enough to cause the SPROC to automatically be rolled back.

I'd like to see some other's opinions on this...

I know that one of my customers runs a huge SPROC daily - commiting the adjudication of medical claims - SPROC returns a RECORDSET of the REGISTER to be printed. We have the printer setup to start printing after the entire document is received (not spooling)...

If the printer hangs - the server for the printer runs out of room - something like that - the whole adjudication is rolled back. When this happens they usually kill the process on the terminal server or re-boot the client machine...

Keep in mind that the SPROC to adjudicate is called within the SPROC that returns the recordset for printing. So with that experience I've been of the belief that if the server cannot completely return the results of a SPROC - it rolls back. So it must be detecting connection lost prior to SPROC completion...

I have seen posts on how to get a SPROC to run asyncronously - but I've never tried it and don't know if the SPROC running async still has this "am-I-connected-to-client-still" need...

[Mark Gambo]

Hmmm, I am learning a lot about SPROCS today, thanks!