USE msdb
GO
IF OBJECT_ID('dbo.usp_WaitForJobs') IS NULL
EXEC ('CREATE PROCEDURE dbo.usp_WaitForJobs AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.usp_WaitForJobs
@JobName_01 VARCHAR(256) = NULL,
@JobName_02 VARCHAR(256) = NULL,
@JobName_03 VARCHAR(256) = NULL,
@JobName_04 VARCHAR(256) = NULL,
@JobId_01 UNIQUEIDENTIFIER = NULL,
@JobId_02 UNIQUEIDENTIFIER = NULL,
@JobId_03 UNIQUEIDENTIFIER = NULL,
@JobId_04 UNIQUEIDENTIFIER = NULL,
@WaitSeconds TINYINT = 5,
@MaxWaitCount INT = 1000,
@PrintSQL BIT = 0,
@ExecuteSQL BIT = 1,
@RaiseError BIT = 1
AS
DECLARE @DelayStr VARCHAR(30) = '00:00:' + RIGHT('0' + CAST(@WaitSeconds AS VARCHAR), 2),
@SQL VARCHAR(MAX) ;
WITH SQL
AS (SELECT ' EXEC msdb.dbo.usp_GetJobExecutionStatus
@job_id = ''' + JobId + ''',
@execution_status = @execution_status OUTPUT,
@is_sysadmin = @is_sysadmin
IF @execution_status <> 4
BEGIN
SELECT @StatusDescription = ''Job: ' + name + ' (' + JobId + '), Status: ''
+ CAST(@execution_status AS VARCHAR) + '', Description: ''
+ CASE @execution_status
WHEN 0 THEN ''Returns only those jobs that are not idle or suspended''
WHEN 1 THEN ''Executing''
WHEN 2 THEN ''Waiting for thread''
WHEN 3 THEN ''Between retries''
WHEN 4 THEN ''Idle''
WHEN 5 THEN ''Suspended''
WHEN 7 THEN ''Performing completion actions''
ELSE ''UNKNOWN''
END + '', Time: '' + CONVERT(VARCHAR(30), GETDATE(), 109)
PRINT @StatusDescription
WAITFOR DELAY ''' + @DelayStr + '''
CONTINUE
END
' AS SQL
FROM msdb.dbo.sysjobs_view (NOLOCK)
CROSS APPLY (SELECT CAST( JOB_ID AS VARCHAR (256)) AS JobId) j
WHERE name LIKE @JobName_01
OR name LIKE @JobName_02
OR name LIKE @JobName_03
OR name LIKE @JobName_04
OR job_id IN (@JobId_01, @JobId_02, @JobId_03, @JobId_04))
SELECT @SQL = 'DECLARE @execution_status INT = -1,
@StatusDescription VARCHAR(1000),
@Error VARCHAR(1000),
@MaxWaitCount int = ' + CAST(@MaxWaitCount AS VARCHAR) + ',
@CurrentWaitCount int = 0,
@is_sysadmin INT = ISNULL(IS_SRVROLEMEMBER(N''sysadmin''), 0)
WHILE @execution_status <> 4
BEGIN
IF @MaxWaitCount <= @CurrentWaitCount
BEGIN
SET @Error = ''MaxWaitCount (' + CAST(@MaxWaitCount AS VARCHAR)
+ ') reached, Time: '' + CONVERT(VARCHAR(30), GETDATE(), 109)
RAISERROR(@Error, 16, 1)
BREAK
END
SET @CurrentWaitCount = @CurrentWaitCount + 1
' + Util.dbo.StringConcat(SQL, '
') + '
END
'
FROM SQL
OPTION (RECOMPILE)
IF @SQL <> ''
BEGIN
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @ExecuteSQL = 1
EXEC(@SQL)
END
ELSE
BEGIN
IF @RaiseError = 1
RAISERROR('usp_WaitForJobs. No job found!', 16, 1)
RETURN 10
END
RETURN 0
GO
GRANT EXECUTE ON [dbo].[usp_WaitForJobs] TO [SQLAgentUserRole]
GRANT VIEW DEFINITION ON [dbo].[usp_WaitForJobs] TO [SQLAgentUserRole]
GRANT EXECUTE ON [dbo].[usp_WaitForJobs] TO [SQLAgentReaderRole]
GRANT VIEW DEFINITION ON [dbo].[usp_WaitForJobs] TO [SQLAgentReaderRole]
GRANT EXECUTE ON [dbo].[usp_WaitForJobs] TO [SQLAgentOperatorRole]
GRANT VIEW DEFINITION ON [dbo].[usp_WaitForJobs] TO [SQLAgentOperatorRole]
GO
/*
EXEC dbo.usp_WaitForJobs
@JobId_01 = '0101211B-5A9B-45EA-B064-2094E880DAD7',
@PrintSQL = 1,
@ExecuteSQL = 1
*/
GO