Run jobs synchronously
If you use sp_start_job you have noticed that the code will continue ro run and the job you started is run asynchrously.
What if you want to wait for the job to finished?
Try this
CREATE PROCEDURE dbo.usp_Start_And_Wait_For_Job
(
@jobName SYSNAME
)
AS
SET NOCOUNT ON
DECLARE @jobID UNIQUEIDENTIFIER,
@maxID INT,
@status INT,
@rc INT
IF @jobName IS NULL
BEGIN
RAISERROR('Parameter @jobName have no value.', 16, 1)
RETURN -100
END
SELECT @jobID = job_id
FROM msdb..sysjobs
WHERE name = @jobName
IF @@ERROR <> 0
BEGIN
RAISERROR('Error when returning jobID for job %s.', 18, 1, @jobName)
RETURN -110
END
IF @jobID IS NULL
BEGIN
RAISERROR('Job %s does not exist.', 16, 1, @jobName)
RETURN -120
END
SELECT @maxID = MAX(instance_id)
FROM msdb..sysjobhistory
WHERE job_id = @jobID
AND step_id = 0
IF @@ERROR <> 0
BEGIN
RAISERROR('Error when reading history for job %s.', 18, 1, @jobName)
RETURN -130
END
SET @maxID = COALESCE(@maxID, -1)
EXEC @rc = msdb..sp_start_job@job_name = @jobName
IF @@ERROR <> 0 OR @rc <> 0
BEGIN
RAISERROR('Job %s did not start.', 18, 1, @jobName)
RETURN -140
END
WHILE (SELECT MAX(instance_id) FROM msdb..sysjobhistory WHERE job_id = @jobID AND step_id = 0) = @maxID
WAITFOR DELAY '00:00:01'
SELECT @maxID = MAX(instance_id)
FROM msdb..sysjobhistory
WHERE job_id = @jobID
AND step_id = 0
IF @@ERROR <> 0
BEGIN
RAISERROR('Error when reading history for job %s.', 18, 1, @jobName)
RETURN -150
END
SELECT @status = run_status
FROM msdb..sysjobhistory
WHERE instance_id = @maxID
IF @@ERROR <> 0
BEGIN
RAISERROR('Error when reading status for job %s.', 18, 1, @jobName)
RETURN -160
END
IF @status <> 1
BEGIN
RAISERROR('Job %s returned with an error.', 16, 1, @jobName)
RETURN -170
END
RETURN 0
Legacy Comments
|
re: Run jobs synchronously
2009-03-25 |
re: Run jobs synchronously re: Run jobs synchronously |
|
Andrew Jens
2010-09-23 |
re: Run jobs synchronously Thanks for the above, but the code will not work if the job is brand new (ie no instance in the jobhistory table). Try something like this (instead of the two-line WHILE loop): --=========================================================== declare @iMaxInstanceID int -- put this up the top select @iMaxInstanceID = MAX(instance_id) FROM msdb..sysjobhistory WHERE job_id = @jobID AND step_id = 0 set @iMaxInstanceID = ISNULL(@iMaxInstanceID, -1) while (@iMaxInstanceID = @maxID) begin WAITFOR DELAY '00:00:01' select @iMaxInstanceID = MAX(instance_id) FROM msdb..sysjobhistory WHERE job_id = @jobID AND step_id = 0 set @iMaxInstanceID = ISNULL(@iMaxInstanceID, -1) end --=========================================================== |
|
Jeremy
2011-04-29 |
re: Run jobs synchronously Hrm, I tried to copy and paste this into a SP and it didn't like it: Msg 102, Level 15, State 1, Procedure usp_Start_And_Wait_For_Job, Line 56 Incorrect syntax near '='. |
|
Pete Midgley
2011-06-13 |
re: Run jobs synchronously Yes, there is a space missed out. just replace that exec line by EXEC @rc = msdb..sp_start_job @job_name = @jobName |
|
G
2011-08-19 |
re: Run jobs synchronously Was looking a solution to a problem I was having, and lo and behold I found it on this page. Many thanks chap G |