sp_sp_start_job_with_wait

/****** Object: StoredProcedure [dbo].[sp_sp_start_job_wait] Script Date: 11.05.2021 13:22:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_sp_start_job_with_wait] (
@job_name SYSNAME,
@WaitTime DATETIME = ’00:00:05′, — this is parameter for check frequency
@JobCompletionStatus INT = null OUTPUT
)
AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

— DECLARE @job_name sysname
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_owner sysname

–Createing TEMP TABLE
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, — BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, — BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

SELECT @job_id = job_id FROM msdb.dbo.sysjobs
WHERE name = @job_name

SELECT @job_owner = SUSER_SNAME()

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

— Start the job if the job is not running
IF NOT EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = 1)
EXEC msdb.dbo.sp_start_job @job_name = @job_name

— Give 2 sec 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 WHERE running = 1)
BEGIN

WAITFOR DELAY @WaitTime

— Information
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 @JobCompletionStatus = run_status
FROM msdb.dbo.sysjobhistory
WHERE job_id = @job_id

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

RETURN @JobCompletionStatus
GO