x

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

Supriya gravatar image

Supriya
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

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x38

asked: Mar 02, 2011 at 12:09 AM

Seen: 720 times

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