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.

more ▼

asked Mar 02, 2011 at 12:09 AM in Default

avatar image

11 1 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, 2011 at 01:12 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Mar 02, 2011 at 12:27 AM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

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

Mar 02, 2011 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, 2011 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, 2011 at 01:18 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 02, 2011 at 12:09 AM

Seen: 890 times

Last Updated: Mar 02, 2011 at 04:57 AM

Copyright 2018 Redgate Software. Privacy Policy