How can I check if it is currently executing?

Here's a good one.
Does any one out there know how to tell if a "particular" (by name) stored p
rocedure is currently being executed?
I have a store procedure that does some processing that takes a long time to
execute (20-30 min)
I need to restrict the calling of this stored procedure if it is currently e
xecuting. (I don't want to call it twice).
The procedure is being called from a client app.
How can I check if it is currently executing?
(BTW SQL Server 2000)
(client VFP)
Thanks All.
RobertRobert
There may be system stored procedures that return this info but surely the l
ogical workaround is just to use your own stored value. You are, after all,
working with an RDBMS!
CREATE TABLE long_running_sp_status ( exec_status bit not null )
GO
CREATE PROCEDURE long_running_sp
AS
IF EXISTS(SELECT 1 FROM long_running_sp_status WHERE exec_status = 1)
BEGIN /* already running */
RETURN
END
/* not running */
BEGIN TRANSACTION
UPDATE long_running_sp_status SET exec_status = 1
...
etc
UPDATE long_running_sp_status SET exec_status = 0
COMMIT TRANSACTION
GO
Regards,
Robert
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:ecAhfLeTGHA.474
0@.TK2MSFTNGP14.phx.gbl...
Here's a good one.
Does any one out there know how to tell if a "particular" (by name) stored p
rocedure is currently being executed?
I have a store procedure that does some processing that takes a long time to
execute (20-30 min)
I need to restrict the calling of this stored procedure if it is currently e
xecuting. (I don't want to call it twice).
The procedure is being called from a client app.
How can I check if it is currently executing?
(BTW SQL Server 2000)
(client VFP)
Thanks All.
Robert|||We are currently using a process similar to this. but the problem is...
If the procedure for any reason crashes or does not complete, the "Currently
Running" flag does not get set back to False
Any Ideas?.?.?.
Robert
"Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message news:%23EuBy
TeTGHA.5496@.TK2MSFTNGP11.phx.gbl...
Robert
There may be system stored procedures that return this info but surely the l
ogical workaround is just to use your own stored value. You are, after all,
working with an RDBMS!
CREATE TABLE long_running_sp_status ( exec_status bit not null )
GO
CREATE PROCEDURE long_running_sp
AS
IF EXISTS(SELECT 1 FROM long_running_sp_status WHERE exec_status = 1)
BEGIN /* already running */
RETURN
END
/* not running */
BEGIN TRANSACTION
UPDATE long_running_sp_status SET exec_status = 1
...
etc
UPDATE long_running_sp_status SET exec_status = 0
COMMIT TRANSACTION
GO
Regards,
Robert
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:ecAhfLeTGHA.474
0@.TK2MSFTNGP14.phx.gbl...
Here's a good one.
Does any one out there know how to tell if a "particular" (by name) stored p
rocedure is currently being executed?
I have a store procedure that does some processing that takes a long time to
execute (20-30 min)
I need to restrict the calling of this stored procedure if it is currently e
xecuting. (I don't want to call it twice).
The procedure is being called from a client app.
How can I check if it is currently executing?
(BTW SQL Server 2000)
(client VFP)
Thanks All.
Robert|||Hi
Why does it "crash"? It shouldn't crash... what exactly happens? Do you t
hink there is a design or implementation flaw you need to address?
If the SP can be set-up as a job to be run by Server Agent, and if it is onl
y ever going to be run by the agent on a specified schedule, you could use s
p_jobhelp (or whatever its called) to check if the *job* has stopped or is s
till running...
Robert
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:uCaLYdeTGHA.434
0@.tk2msftngp13.phx.gbl...
We are currently using a process similar to this. but the problem is...
If the procedure for any reason crashes or does not complete, the "Currently
Running" flag does not get set back to False
Any Ideas?.?.?.
Robert
"Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message news:%23EuBy
TeTGHA.5496@.TK2MSFTNGP11.phx.gbl...
Robert
There may be system stored procedures that return this info but surely the l
ogical workaround is just to use your own stored value. You are, after all,
working with an RDBMS!
CREATE TABLE long_running_sp_status ( exec_status bit not null )
GO
CREATE PROCEDURE long_running_sp
AS
IF EXISTS(SELECT 1 FROM long_running_sp_status WHERE exec_status = 1)
BEGIN /* already running */
RETURN
END
/* not running */
BEGIN TRANSACTION
UPDATE long_running_sp_status SET exec_status = 1
...
etc
UPDATE long_running_sp_status SET exec_status = 0
COMMIT TRANSACTION
GO
Regards,
Robert
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:ecAhfLeTGHA.474
0@.TK2MSFTNGP14.phx.gbl...
Here's a good one.
Does any one out there know how to tell if a "particular" (by name) stored p
rocedure is currently being executed?
I have a store procedure that does some processing that takes a long time to
execute (20-30 min)
I need to restrict the calling of this stored procedure if it is currently e
xecuting. (I don't want to call it twice).
The procedure is being called from a client app.
How can I check if it is currently executing?
(BTW SQL Server 2000)
(client VFP)
Thanks All.
Robert|||check out sp_getapplock in BOL.|||rmg66 (rgwathney__xXx__primepro.com) writes:
> Here's a good one.
> Does any one out there know how to tell if a "particular" (by name)
> stored procedure is currently being executed?
One could inspect sysprocess.sql_handle for every process, but if the
procedure has called an inner procedure you would not see it.
Anyway, Scott had the right answer for you: use Application locks.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm sorry if I'm being dense. But I don't quite understand how this (sp_geta
pplock) is to be used.
Will it lock access to any db object (e.g. a particular stored procedure) un
til it is unlocked?
When I tested it. It didn't seeem to do what I expected...
I used it to lock a table object and then attempted to update the table. I d
id it inside a transaction.
I expected the table to reject the update but it didn't.
Am I missing something?
Can someone give me an example of how I could use this to prevent and stored
procedure to be called while it is currently executing?
Thanks, Robert
"Scott Morris" <bogus@.bogus.com> wrote in message news:ePqOKneTGHA.4264@.TK2MSFTNGP11.phx.gb
l...
> check out sp_getapplock in BOL.
>|||> Will it lock access to any db object (e.g. a particular stored procedure)
> until it is unlocked?
It doesn't really "lock" anything. It effectively sets a flag that can be
used as a gatekeeper. If not used correctly, it will not be useful.

> When I tested it. It didn't seeem to do what I expected...
> I used it to lock a table object and then attempted to update the table. I
> did it inside a transaction.
> I expected the table to reject the update but it didn't.
> Am I missing something?
Yes - the whole idea is to serialize processing. If you did not attempt to
execute the procedure simultaneously from multiple connections, then you
only tested the syntactical correctness of your usage (details of which you
did not post so we cannot determine if you used it correctly).

> Can someone give me an example of how I could use this to prevent and
> stored procedure to be called while it is currently executing?
BOL has some important information. In addition, you can search the
newsgroups (as well as the internet) for additional information and
examples. Note that it will not PREVENT a "... stored procedure to be
called..."; it can be used to coordinate simultaneous executions of the
procedure in whatever manner you desire.|||sp_getapplock doesn't lock any data inside the database, it is just there fo
r *you* to use to define
a lock using SQL Servers lock semantics and release the lock at will.
ALTER PROC p AS
BEGIN TRAN
EXEC sp_getapplock @.Resource = 'myLock',
@.LockMode = 'Exclusive'
RAISERROR('Before', 10, 1) WITH NOWAIT
WAITFOR DELAY '00:00:20'
EXEC sp_releaseapplock @.Resource = 'myLock'
COMMIT
RAISERROR('After', 10, 1) WITH NOWAIT
GO
EXEC p
--Now, within 20 seconds, execute this from another connection
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:uTWo0qoTGHA.2276@.tk2msftngp13.phx.gbl...
> I'm sorry if I'm being dense. But I don't quite understand how this (sp_ge
tapplock) is to be used.
> Will it lock access to any db object (e.g. a particular stored procedure)
until it is unlocked?
> When I tested it. It didn't seeem to do what I expected...
> I used it to lock a table object and then attempted to update the table. I
did it inside a
> transaction.
> I expected the table to reject the update but it didn't.
> Am I missing something?
> Can someone give me an example of how I could use this to prevent and stor
ed procedure to be
> called while it is currently executing?
> Thanks, Robert
>
>
> "Scott Morris" <bogus@.bogus.com> wrote in message news:ePqOKneTGHA.4264@.TK
2MSFTNGP11.phx.gbl...
>|||Thanks All
and thanks Tibor for that the example of how it works...
Robert
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:eL
TjZKpTGHA.196@.TK2MSFTNGP10.phx.gbl...
> sp_getapplock doesn't lock any data inside the database, it is just there
for *you* to use to define a lock using SQL Servers lock
> semantics and release the lock at will.
> ALTER PROC p AS
> BEGIN TRAN
> EXEC sp_getapplock @.Resource = 'myLock',
> @.LockMode = 'Exclusive'
> RAISERROR('Before', 10, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:20'
> EXEC sp_releaseapplock @.Resource = 'myLock'
> COMMIT
> RAISERROR('After', 10, 1) WITH NOWAIT
> GO
> EXEC p
> --Now, within 20 seconds, execute this from another connection
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message news:uTWo0qoTGHA.2
276@.tk2msftngp13.phx.gbl...
>