Require a single instance of a Stored Procedure
-
Using Microsoft SQL 2005 Express I need to be able to prevent the same stored procedure from running more than once at the same time so that if the stored procedure is in progress it does not run again. Although nothing will break if this occurs the second run will be redundant. I would like to ensure that any unnecessary work performed by the SQL Server is prevented. The Stored Procedure in question performs some housekeeping functionality that has a non-deterministic duration. I would like to do this all within SQL 2005 Express. Any help is appreciated. Thank you in advance for your time.
Ant. **I'm hard, yet soft.
I'm coloured, yet clear.
I'm fruity and sweet.
I'm jelly, what am I? Muse on it further, I shall return!**- David Walliams (Little Britain)
-
Using Microsoft SQL 2005 Express I need to be able to prevent the same stored procedure from running more than once at the same time so that if the stored procedure is in progress it does not run again. Although nothing will break if this occurs the second run will be redundant. I would like to ensure that any unnecessary work performed by the SQL Server is prevented. The Stored Procedure in question performs some housekeeping functionality that has a non-deterministic duration. I would like to do this all within SQL 2005 Express. Any help is appreciated. Thank you in advance for your time.
Ant. **I'm hard, yet soft.
I'm coloured, yet clear.
I'm fruity and sweet.
I'm jelly, what am I? Muse on it further, I shall return!**- David Walliams (Little Britain)
Hi, You could use locking for this. Create a small table and insert a single row in it. In the beginning of the procedure, update the row in the table to acquire lock and in the end of the procedure commit your changes. For example:
CREATE TABLE ProcedureRun (LastRun datetime);
INSERT INTO ProcedureRun (LastRun) VALUES (NULL);
CREATE PROCEDURE SomeProcedure AS
BEGIN
UPDATE ProcedureRun SET LastRun = GETDATE();... do some stuff...
COMMIT;
END;Hope this helps, Mika
-
Hi, You could use locking for this. Create a small table and insert a single row in it. In the beginning of the procedure, update the row in the table to acquire lock and in the end of the procedure commit your changes. For example:
CREATE TABLE ProcedureRun (LastRun datetime);
INSERT INTO ProcedureRun (LastRun) VALUES (NULL);
CREATE PROCEDURE SomeProcedure AS
BEGIN
UPDATE ProcedureRun SET LastRun = GETDATE();... do some stuff...
COMMIT;
END;Hope this helps, Mika
Thank you Mika, I have decided to take your advise. The concept of creating a "control table" seems a good one.
Ant. **I'm hard, yet soft.
I'm coloured, yet clear.
I'm fruity and sweet.
I'm jelly, what am I? Muse on it further, I shall return!**- David Walliams (Little Britain)
-
Thank you Mika, I have decided to take your advise. The concept of creating a "control table" seems a good one.
Ant. **I'm hard, yet soft.
I'm coloured, yet clear.
I'm fruity and sweet.
I'm jelly, what am I? Muse on it further, I shall return!**- David Walliams (Little Britain)