CREATE PROCEDURE dbo.usp_StartJobSynchronous
@job_name SYSNAME,
@step_name SYSNAME = NULL,
@execution_status INT = 0 OUTPUT,
@JobResult VARCHAR(MAX) = NULL OUTPUT,
@job_id UNIQUEIDENTIFIER = NULL OUTPUT,
@is_sysadmin INT = NULL OUTPUT,
@PrintResult BIT = 1,
@GenerateErrorOnDisabled BIT = 0,
@GenerateErrorOnRunning BIT = 1
AS
SET NOCOUNT ON
DECLARE @job_owner SYSNAME,
@ReturnVal INT,
@run_date INT,
@run_time INT,
@run_status INT,
@ErrorMessage VARCHAR(MAX),
@enabled INT
/*
Is the execution status for the jobs.
Value Description
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
*/
SELECT @job_id = job_id,
@is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0),
@job_owner = SUSER_SNAME(),
@enabled = enabled
FROM msdb..sysjobs_view (NOLOCK)
WHERE name = @job_name
IF @@ROWCOUNT = 0
BEGIN
SET @ErrorMessage = 'Job "' + @job_name + '" does not exist'
RAISERROR (@ErrorMessage,
16,
1)
RETURN 16
END
IF @GenerateErrorOnDisabled = 0
AND @enabled = 0
BEGIN
PRINT 'Job "' + @job_name + '" is disabled'
RETURN
END
RETURN
EXEC @ReturnVal = msdb.dbo.usp_GetJobExecutionStatus
@job_id = @job_id,
@job_name = @job_name,
@job_owner = @job_owner,
@is_sysadmin = @is_sysadmin,
@execution_status = @execution_status OUTPUT
--SELECT @ReturnVal AS '@ReturnVal',
-- @job_name AS '@job_name',
-- @job_id AS '@job_id',
-- @is_sysadmin AS '@is_sysadmin',
-- @execution_status AS '@execution_status'
IF @execution_status <> 4
BEGIN
SET @ErrorMessage = 'Cannot start Job "' + @job_name + '" (' + CAST(@job_id AS VARCHAR(256)) + ')' + ISNULL(' step "' + @step_name + '"', '')
+ '. It is in status ' + CAST(@execution_status AS VARCHAR) + ' ' + 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
IF @GenerateErrorOnRunning = 0
AND @execution_status = 1
BEGIN
PRINT 'Job "' + @job_name + '" is already running'
RETURN
END
RAISERROR (@ErrorMessage,
16,
1)
RETURN 10
END
SET @execution_status = 1
EXEC @ReturnVal = msdb.dbo.sp_start_job
@job_id = @job_id,
@step_name = @step_name
IF @ReturnVal <> 0
BEGIN
SET @ErrorMessage = 'Failed to start Job "' + @job_name + '"' + ISNULL(' step "' + @step_name + '"', '') + '. EXEC msdb.dbo.sp_start_job returned '
+ CAST(@ReturnVal AS VARCHAR)
RAISERROR (@ErrorMessage,
16,
1)
RETURN @ReturnVal
END
WHILE @execution_status <> 4
BEGIN
WAITFOR DELAY '00:00:05'
EXEC @ReturnVal = msdb.dbo.usp_GetJobExecutionStatus
@job_id = @job_id,
@job_name = @job_name,
@job_owner = @job_owner,
@is_sysadmin = @is_sysadmin,
@execution_status = @execution_status OUTPUT
--SELECT @ReturnVal AS '@ReturnVal',
-- @job_id AS '@job_id',
-- @job_name AS '@job_name',
-- @is_sysadmin AS '@is_sysadmin',
-- @execution_status AS '@execution_status'
END
SELECT TOP 1
@run_date = run_date,
@run_time = run_time,
@run_status = run_status,
@JobResult = MESSAGE
FROM [msdb].[dbo].[sysjobhistory] jh (NOLOCK)
WHERE job_id = @job_id
AND step_id = 0
ORDER BY instance_id DESC
IF @run_status <> 1 -- 1 = Succeeded
BEGIN;
WITH FirstInstance
AS (SELECT MIN(instance_id) AS instance_id
FROM [msdb].[dbo].[sysjobhistory] jh (NOLOCK)
WHERE job_id = @job_id
AND step_id > 0
AND run_date = @run_date
AND run_time = @run_time)
SELECT @JobResult = @JobResult + '
job_name: ' + @job_name + '
step_id: ' + CAST(step_id AS VARCHAR) + '
step_name: ' + step_name + '
run_start: ' + CONVERT(VARCHAR(30), RunDateTime, 120) + '
run_duration: ' + CAST(run_duration AS VARCHAR) + '
job_id: ' + CAST(@job_id AS VARCHAR(100)) + '
' + Util.dbo.StringConcat(MESSAGE, '
')
FROM [msdb].[dbo].[sysjobhistory] jh (NOLOCK)
CROSS APPLY (SELECT CAST(CAST(jh.[run_date] AS VARCHAR) AS DATETIME)
+ CAST(CAST(STUFF(STUFF(RIGHT('0' + CAST(jh.[run_time] AS VARCHAR), 6), 3, 0, ':'), 6, 0, ':') AS TIME) AS DATETIME) AS RunDateTime) rd
WHERE job_id = @job_id
AND step_id > 0
AND jh.instance_id >= (SELECT instance_id FROM FirstInstance)
AND run_status <> 1 -- 1 = Succeeded
GROUP BY step_id,
step_name,
run_date,
run_time,
run_duration,
RunDateTime
IF @PrintResult = 1
EXEC Util.dbo.PrintLargeText
@JobResult
SET @ErrorMessage = 'Job "' + @job_name + '" failed'
RAISERROR (@ErrorMessage,
16,
1)
RETURN 12
END
IF @PrintResult = 1
EXEC Util.dbo.PrintLargeText
@JobResult
RETURN 0