USE msdb
GO
IF OBJECT_ID('dbo.usp_GetJobExecutionStatus') IS NULL
EXEC ('CREATE PROCEDURE dbo.usp_GetJobExecutionStatus AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.usp_GetJobExecutionStatus
@job_name SYSNAME = NULL OUTPUT,
@execution_status INT OUTPUT,
@job_id UNIQUEIDENTIFIER = NULL OUTPUT,
@is_sysadmin INT = NULL OUTPUT,
@job_owner SYSNAME = NULL OUTPUT,
@execution_status_desc VARCHAR(256) = NULL OUTPUT,
@requested_to_run INT = NULL OUTPUT,
@request_source INT = NULL OUTPUT,
@request_source_id SYSNAME = NULL OUTPUT,
@current_step INT = NULL OUTPUT,
@current_retry_attempt INT = NULL OUTPUT,
@Rowcount INT = NULL OUTPUT,
@RaiseErrorIfJobNotFound BIT = 1
AS
SET NOCOUNT ON
/*
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.
*/
IF NOT (@job_id IS NOT NULL
AND @job_name IS NOT NULL
AND @is_sysadmin IS NOT NULL
AND @job_owner IS NOT NULL)
BEGIN
SELECT @job_id = job_id,
@job_name = name,
@is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0),
@job_owner = SUSER_SNAME()
FROM msdb..sysjobs_view (NOLOCK)
WHERE (name = @job_name
AND @job_name IS NOT NULL
AND @job_id IS NULL)
OR (job_id = @job_id
AND @job_id IS NOT NULL
AND @job_name IS NULL)
OPTION (RECOMPILE)
IF @@ROWCOUNT <> 1
BEGIN
IF @RaiseErrorIfJobNotFound = 1
BEGIN
DECLARE @Error VARCHAR(1000) = 'Failed to locate @job_name: ' + ISNULL(@job_name, 'NULL')
+ ', @job_id: ' + ISNULL(CAST(@job_id AS VARCHAR(128)), 'NULL')
RAISERROR(@Error, 16, 1)
END
RETURN 10
END
END
DECLARE @xp_results TABLE (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)
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs
@is_sysadmin,
@job_owner,
@job_id
SET @Rowcount = @@ROWCOUNT
SELECT TOP 1
@execution_status = job_state,
@execution_status_desc = CASE job_state
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,
@current_step = current_step,
@requested_to_run = requested_to_run,
@request_source = request_source,
@request_source_id = request_source_id,
@current_step = current_step,
@current_retry_attempt = current_retry_attempt
FROM @xp_results
RETURN 0
GO
GRANT EXECUTE ON [dbo].[usp_GetJobExecutionStatus] TO [SQLAgentUserRole]
GRANT VIEW DEFINITION ON [dbo].[usp_GetJobExecutionStatus] TO [SQLAgentUserRole]
GRANT EXECUTE ON [dbo].[usp_GetJobExecutionStatus] TO [SQLAgentReaderRole]
GRANT VIEW DEFINITION ON [dbo].[usp_GetJobExecutionStatus] TO [SQLAgentReaderRole]
GRANT EXECUTE ON [dbo].[usp_GetJobExecutionStatus] TO [SQLAgentOperatorRole]
GRANT VIEW DEFINITION ON [dbo].[usp_GetJobExecutionStatus] TO [SQLAgentOperatorRole]
GO
/*
SELECT 'GRANT EXECUTE ON [dbo].[usp_GetJobExecutionStatus] TO [' + name + ']
GRANT VIEW DEFINITION ON [dbo].[usp_GetJobExecutionStatus] TO [' + name + ']
'
FROM sys.database_principals
WHERE name LIKE 'sqlage%'
*/