If you ever had a need to start a SQL job using sp_start_job stored procedure in msdb then you know once the job is started successfully, it returns the control back to the user immediately and does not wait for the job to complete.
But what if you want your script to wait for the job to complete and then do some other stuff?
Here is a script I have written a while back that you can use to start any SQL job job. The script will wait for the job to complete and returns job completion code back to you.
I actually have this as a stored procedure but for the demo purpose here it is in a script format.
Hope you find this useful!
SET TRANSACTION ISOLATIONLEVEL READ UNCOMMITTED
SET NOCOUNT ON
DECLARE
@job_name SYSNAME = 'test', -- INPUT YOUR JOB NAME HERE
@WaitTime DATETIME = '00:00:05', -- default check frequency
@JobCompletionStatus INT
-- CHECK IF IT IS A VALID AND EXISTING JOB NAME
IF NOT EXISTS (SELECT * FROM msdb..sysjobs WHERE name =@job_name)
BEGIN
RAISERROR ('[ERROR]:[%s] job does not exist. Please check',16, 1, @job_name) WITH LOG
RETURN
END
DECLARE@job_id UNIQUEIDENTIFIER
DECLARE@job_owner sysname
--Createing TEMP TABLE
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOTNULL,
last_run_time INT NOTNULL,
next_run_date INT NOTNULL,
next_run_time INT NOTNULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOTNULL, -- BOOL
request_source INT NOTNULL,
request_source_id sysname COLLATEdatabase_default NULL,
running INT NOTNULL, -- BOOL
current_step INT NOTNULL,
current_retry_attempt INT NOT NULL,
job_state INT NOTNULL)
SELECT@job_id = job_id FROMmsdb.dbo.sysjobs WHERE name =@job_name
SELECT@job_owner = SUSER_SNAME()
INSERT INTO #xp_results EXECUTEmaster.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
-- Start the job only if it is not already running
IF NOT EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = 1)
EXEC msdb.dbo.sp_start_job @job_name = @job_name
-- Give it 2 seconds for think time.
WAITFOR DELAY '00:00:02'
DELETE FROM #xp_results
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
WHILE EXISTS(SELECT TOP 1 * FROM #xp_results WHERErunning = 1)
BEGIN
WAITFOR DELAY @WaitTime
-- Display informational message at each interval
raiserror('JOB IS RUNNING', 0, 1 ) WITH NOWAIT
DELETE FROM #xp_results
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
END
SELECT top 1 @JobCompletionStatus =run_status FROMmsdb.dbo.sysjobhistory
WHEREjob_id = @job_id ANDstep_id = 0
order by run_date desc, run_time desc
IF@JobCompletionStatus = 1
PRINT 'The job ran Successful'
ELSE IF @JobCompletionStatus =3
PRINT 'The job is Cancelled'
ELSE
BEGIN
RAISERROR ('[ERROR]:%s job is either failed or not in good state. Please check',16, 1, @job_name) WITH LOG
END
