Force a SPROC to run non-concurrently

Hi,
I have a sproc that needs to run, the only thing is, I only want it to =
always be executing non-concurrently, i.e., this sproc should never be =
running more that one time at any given time. (This sproc is making =
external calls which will break if ran concurrently). Does anyone have =
any ideas about how to accomplish this?
--Michael"Raterus" <raterus@.hotmail.com> wrote in message
news:%237KrGFbRFHA.3288@.TK2MSFTNGP14.phx.gbl...
Hi,

> I have a sproc that needs to run, the only thing is,
> I only want it to always be executing non-concurrently,
> i.e., this sproc should never be running more that one
> time at any given time. (This sproc is making external
> calls which will break if ran concurrently). Does anyone
> have any ideas about how to accomplish this?
Write a row to a table to say "Proc running." If there's an entry in the
table when the proc starts, return an error.
Steve|||> Write a row to a table to say "Proc running." If there's an entry in the
> table when the proc starts, return an error.
And make sure the proc resets the value, and also that you handle the case
when the proc aborts--else a failure will prevent the proc from ever running
again until you manually reset the flag (which may in fact be a requirement,
e.g. don't let it run again until we fix whatever made it fail).
A|||I've actually explored that idea, I'd write "Proc running" when it =
starts and "All Done" when it finishes. My only worries is that the =
sproc ever fails it would never write "All Done", which will backlog my =
process, and cause a ton of problems. Any way to guarantee that the =
proc will write "All Done" at the end, no matter what happens in the =
procedure?
"Steve" <steve@.somewhere.invalid.com> wrote in message =
news:Vjt9e.4937749$Zm5.767709@.news.easynews.com...
> "Raterus" <raterus@.hotmail.com> wrote in message
> news:%237KrGFbRFHA.3288@.TK2MSFTNGP14.phx.gbl...
> Hi,
>=20
>=20
> Write a row to a table to say "Proc running." If there's an entry in =
the
> table when the proc starts, return an error.
>=20
> --=20
> Steve
>=20
>|||You an use an application lock in your stored procedure. See sp_getapplock
in Books Online for the details. It has a couple of advantages, like
settings lock time-out periods, so the second instance of the stored
procedure can wait for the first instance to finish within a certain time
instead of just plain failing.
Jacco Schalkwijk
SQL Server MVP
"Raterus" <raterus@.hotmail.com> wrote in message
news:%237KrGFbRFHA.3288@.TK2MSFTNGP14.phx.gbl...
Hi,
I have a sproc that needs to run, the only thing is, I only want it to
always be executing non-concurrently, i.e., this sproc should never be
running more that one time at any given time. (This sproc is making external
calls which will break if ran concurrently). Does anyone have any ideas
about how to accomplish this?
--Michael