How to start SQL Server job using TSQL and wait for it to complete?

,


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

Rate

Share

Share

Rate