March 8, 2022 at 2:49 pm
We have a stored procedure (with many embedded procedures) that populates a a table with a lot of aggregated data. When populated this table is used to create a series of reports for many users across the large hospital system. The procedure lasts about 5 hours. Please advice how to prevent other users to start the procedure if it is already running.
The procedure starts with truncate table command. Then the population starts.
March 8, 2022 at 2:58 pm
Easiest way would likely be at the start of the proc, write a row to a control table. At the end of the proc, delete that row.
If the proc tries to start and the row already exists, just display a message and exit. Or you could just make the proc_name a unique key and SQL would cause an error for you if someone tried to insert another row with that key.
CREATE PROCEDURE dbo.my_proc
AS
...
IF EXISTS(SELECT 1 FROM dbo.proc_control_table WHERE proc_name = 'my_proc')
BEGIN
RAISERROR('This proc is already running, please wait for it to finish. Cancelling this execution.', 16, 1)
RETURN -1
END
INSERT INTO dbo.proc_control_table ( proc_name, starting_time, original_user )
SELECT 'my_proc', GETDATE(), ORIGINAL_LOGIN()
...
DELETE FROM dbo.proc_control_table
WHERE proc_name = 'my_proc'
RETURN 0
/*end of proc*/
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 8, 2022 at 3:47 pm
Thank you ... We will try it ...
March 8, 2022 at 4:21 pm
Adding a PK to the table, based on the procedure's name, probably make sense anyway. This stops the unlikely scenario of 2 people starting the process almost simultaneously and when the table is check both instances don't see the row and then both INSERT the row. Otherwise you would need to lock the table until such time that you INSERT the row after checking it exists. Plus, if you just try to INSERT the row and it fails, due to the PK violation, you only need 1 scan/seek on the table, not 2.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 8, 2022 at 7:06 pm
We have a stored procedure (with many embedded procedures) that populates a a table with a lot of aggregated data. When populated this table is used to create a series of reports for many users across the large hospital system. The procedure lasts about 5 hours. Please advice how to prevent other users to start the procedure if it is already running.
The procedure starts with truncate table command. Then the population starts.
Use a trigger procedure and a worker procedure, the trigger procedure invokes the worker procedure that then drops the trigger procedure before the work and then re-creates the trigger procedure at the end of the work 😉
😎
March 8, 2022 at 8:05 pm
Scott, just naïve question (you know I'm not a guru ... so far) in your code the code to halt the sp execution is missing ... after we did all raiseerror and insert - how the intruder will be kicked off the execution?
March 8, 2022 at 8:10 pm
The RETURN statement will exit the proc. The -1 is a return code that can be checked by code calling the proc to see if an error occurred.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 8, 2022 at 8:19 pm
Thanks a lot ...
March 8, 2022 at 10:20 pm
Scott,
I tried this code, however have an error for RETURN -1:
Msg 178, Level 15, State 1, Line 6
A RETURN statement with a return value cannot be used in this context.
Advice?
March 8, 2022 at 10:23 pm
Is the code inside a stored proc (as stated in original post)?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 8, 2022 at 10:29 pm
No. Outside. I created a table, inserted one record and then tried to run:
IF EXISTS(SELECT 1 FROM dbo._proc_control_table WHERE Proc_Name = 'sp_Populate_NEW_NEW')
BEGIN
RAISERROR('This proc is already running, please wait for it to finish. Cancelling this execution.', 16, 1)
RETURN -1
END
March 8, 2022 at 10:33 pm
RETURN works only within a stored proc.
You could switch to a GOTO:
IF EXISTS(SELECT 1 FROM dbo._proc_control_table WHERE Proc_Name = 'sp_Populate_NEW_NEW')
BEGIN
RAISERROR('This proc is already running, please wait for it to finish. Cancelling this execution.', 16, 1)
GOTO ExitCode
END
...do more stuff here...
ExitCode:
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 8, 2022 at 10:39 pm
It works inside the sp. Thank you.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply