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

[kay_koll_automic]

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

[Legacy User]

Good post kay_koll_automic

Question - I've tested this before (11.2.4) and from my test, my AE SQL job waits until the my SQL Server Agent job finish. Could it be because my steps are simpler (only does a select *.. some steps take over a minute to complete) and more straightforward, that's why my AE SQL job waited?

What are the steps in your SQL Server Agent job and the command on the steps?
Does your step call/trigger another SQL Server Agent job?
https://us.v-cdn.net/5019921/uploads/editor/1m/5xg26kbqb360.png" width="1317">

Regards,
Christine

[kay_koll_automic]

Hi Christine,
not sure if I understand you correctly. I have made the experience that calling an MS SQL Server Job using the T-SQL command EXEC will return immediately which may cause issues with long time Jobs. 
I found the script in the internet which monitors the 'sysjobhistory' table in order to identify when a MS SQL Server Job is done.
The Steps of a MS SQL Server Job get executed one by one when the Job executed. My script executes the Job itself which executes then the Steps of the Job. I am going to post an updated script which allows to execute a step directly.

[kay_koll_automic]

Here comes an extended version of the script from above. It allows to execute a MS SQL Server Job Step directly. It also prints '#ERROR# in the Job report in case a Job ran in a run time error. It allows you use a Filter Object to change the Automic Job status accordingly .
 
&PS_SQL_JOBNAME# = Name of the MS SQL Server Job
&PS_JOB_STEPNAME# = Optional name of the MS SQL Server Step name.

SET NOCOUNT ON

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


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

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

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

:IF STR_LENGTH(&PS_JOB_STEPNAME#) > 0
  EXEC @rc = msdb..sp_start_job @job_name = '&PS_SQL_JOBNAME#', @step_name = '&PS_JOB_STEPNAME#'
:ELSE
  EXEC @rc = msdb..sp_start_job @job_name = '&PS_SQL_JOBNAME#'
:ENDIF

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

IF @status = 0
BEGIN
PRINT '#ERROR# Microsoft SQL Server Job execution ran in an error'
END


[Legacy User]

Hi Kay,

Please correct me if I'm wrong..
In your post, you use "EXEC {SQLServerAgent_JobName}" to trigger a job, and the purpose of the code you posted is for AutomationEngine SQL Job to wait for SQLServerAgent Job to finish..?
You're referring to the SQL Server Agent Job, and not the SQL Stored Procedure?


This is the test I did:

JOBS.SQL.SQLEXECUTEJOB executes SQL Server Agent job "cch"


"cch" is a job in SQL Server, with 6 steps, later modified to add 2 more steps



Below is the SQL Job history of the two runs I ran today, and the Statistics of JOBS.SQL.SQLEXECUTEJOB



Notice that the duration of "cch" increased from 22 seconds to 52 seconds after I added two more steps.

With 6 steps, it ended at 8:38:32 AM, duration 22 seconds.
AE SQL Job (runid 1765041) ended at 8:38:38 AM, runtime 29 seconds

With 8 steps, it ended at 8:42:37 AM, duration 52 seconds.
AE SQL Job (runid 1765051) ended at 8:42:41 AM, runtime 57 seconds.

I know AE SQL Job does not wait for a SQL Stored Procedure to finish (eg: Exec storedProc_name)
But it looks like AE SQL Job does wait for the SQL Server Agent Job (eg: sql_execute_job JOB="cch")

Regards,
Christine

[kay_koll_automic]

I have to admit that I forget our own SQL Agent functionality.
You are right, this is a better approach.

[Legacy User]

I think what you posted is helpful especially if the steps are a bit more complex (eg: triggers another job or a stored procedure, etc) :smile:

[MuckMoses]

Thanks for this thread it helped me solve a problem I was having.

[Jim Griffith]

If you don't have the SQL agent, you can also use the SQL tools on a host and use the SQLCMD command. I've never had an issue with a job completing until the process has completed on the SQL side. 

sqlcmd -S <sql server> -d <database> -E -Q "exec <sql command or stored proc>"