/* ===============================================================================

Name:
sp_IsJobRunning.sql

Description:
See if a job is running.

Inputs:
@JobName - name of job to see if running.

Outputs:
@IsRunning - job running status.

'Y' = job is running.
'N' = job is not running.

Returns:
1 - error occurred.
0 - no error occurred.

Example:
Declare @IsRunning char(1)
EXEC sp_IsJobRunning "some job name", @IsRunning OUTPUT
IF @IsRunning = 'Y'
Print 'Job is Running'
ELSE
Print 'Job is not Running'


Author: Allan Saywitz, divine inc.

Revision History:
12/10/2001 Created.

=============================================================================== */

IF EXISTS (SELECT *
FROM sysobjects
WHERE id = object_id('sp_IsJobRunning')
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
)
DROP PROC sp_IsJobRunning
GO

CREATE PROC sp_IsJobRunning
(
@JobName varchar(64),
@IsRunning char(1) OUTPUT
)
AS

BEGIN

SET NOCOUNT ON

-- declare variables
DECLARE
@job_id uniqueidentifier,
@is_sysadmin int,
@job_owner sysname,
@execution_status int

-- initialize variables
SET @IsRunning = 'N'
SET @job_id = NULL
SET @is_sysadmin = 0
SET @job_owner = NULL
SET @execution_status = 0

-- set job id
SET @job_id = (SELECT job_id FROM msdb..sysjobs WHERE name = @JobName)
IF (@job_id IS NULL)
BEGIN
RAISERROR('Unknown job name', 16, 1)
RETURN 1
END

-- create 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,
request_source INT NOT NULL,
request_source_id sysname NULL,
running INT NOT
NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)

-- set sysadmin flag
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

-- set job owner
SELECT @job_owner = SUSER_SNAME()

-- populate #xp_results
INSERT INTO #xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

-- set execution status
SET @execution_status = (SELECT job_state FROM #xp_results WHERE job_id = @job_id)

-- set is running flag
IF (@execution_status = 1)
BEGIN
SET @IsRunning = 'Y'
END

-- drop #xp_results
IF EXISTS (SELECT 1 WHERE (OBJECT_ID('tempdb..#xp_results') IS NOT NULL) )
BEGIN
DROP TABLE #xp_results
END

SET NOCOUNT OFF

RETURN 0

END
GO