kay_koll_automic

Executing MS-SQL Job and wait until the job has been finished

Discussion created by kay_koll_automic on Apr 24, 2017
Latest reply on Aug 18, 2017 by Jim Griffith
I found following very useful T-SQL which allows to execute a MS SQL Server Job and then wait until the MS SQL Server has been finished.  It is very useful if you want to execute SQL Server Jobs which depending on each other.

Note: v12 users may also use my Action in Automic Marketplace

SET NOCOUNT ON

DECLARE       @jobID UNIQUEIDENTIFIER,
       @maxID INT,
       @status INT,
       @rc INT


SELECT @jobID = job_id
FROM   msdb..sysjobs
WHERE name = '&jobName#'

SELECT @maxID = MAX(instance_id)
FROM   msdb..sysjobhistory
WHERE job_id = @jobID
       AND step_id = 0

SET    @maxID = COALESCE(@maxID, -1)

EXEC @rc = msdb..sp_start_job @job_name = '&jobName#'

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


SELECT @status = run_status
FROM   msdb..sysjobhistory
WHERE instance_id = @maxID

Outcomes