How to check if a resource is locked?

I am calling a load SP from another SP. I can use sp_getapplock in the Load Sp to ensure single instance run. But is there any way to check from the caller SP if the Load SP is running? I think there could be extended stored procedure to get the information.

asked Mar 02 '11 at 12:09 AM in Default

Supriya
11 ● 1 ● 1

Why? What are you trying to achieve? Are there problems with the length of time it takes the inner SP to run?

Mar 02 '11 at 01:12 AM ThomasRushton ♦

(comments are locked)

1 answer:

Am I right in thinking that you want SProc1 to call SProc2, and, while SProc2 is running, check that SProc2 is actually running, all within the context of SProc1?

Not unless they've made SP calls asynchronous, which, last time I looked, they hadn't.

answered Mar 02 '11 at 12:27 AM

ThomasRushton ♦
30.2k ● 7 ● 10 ● 40

Yes. I want to check from Sproc1 whether Sproc2 is running.

Mar 02 '11 at 12:52 AM Supriya

In which case, erm, no. Not unless your SProc1 does some CLR-type stuff that then calls SProc2, assuming CLR can do multi-threading stuff. @Matt Whitfield is one of AskSSC's in-house experts on CLR. I'll see if I can get him to swing by here...

Mar 02 '11 at 01:10 AM ThomasRushton ♦

You certainly could make a multi-threaded CLR proc to do asynchronous calls (if you are careful and don't mind deploying 'UNSAFE' assemblies). But to check if SProc2 was actually running... Now that would be a problem...

Mar 02 '11 at 01:18 AM Matt Whitfield ♦♦

(comments are locked)