I like your idea but I make a few changes to meet my environment.
CREATE VIEW dbo.v_jobs
AS
SELECT j.name AS JobName,
j.job_id,
CAST (j.job_id AS NCHAR (36)) AS job_id_as_text,
CAST (j.job_id AS BINARY (16)) AS job_id_as_bin
FROM msdb.dbo.sysjobs j
GO
CREATE FUNCTION dbo.uf_HexToChar
(
@binaryValue VARBINARY(100) ,
@numBytes INT
)
RETURNS VARCHAR(200) WITH SCHEMABINDING
AS
/*
we need a hex-to-GUID converter to match job name from sys.dm_exec_sessions to a job_id value.
use dbo.uf_HexToChar (j.job_id, 16)
*/
BEGIN
DECLARE @i VARBINARY(10) ,
@hexdigits CHAR(16) ,
@s-2 VARCHAR(100) ,
@h VARCHAR(100) ,
@currentByte SMALLINT
SET @hexdigits = '0123456789ABCDEF'
SET @currentByte = 0
SET @h = ''
-- process all bytes
WHILE @currentByte < @numBytes
BEGIN
SET @currentByte = @currentByte + 1
-- get first character of byte
SET @i = SUBSTRING(CAST(@binaryValue AS VARBINARY(100)), @currentByte, 1)
-- get the first character
SET @s-2 = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1))
-- shift over one character
SET @i = @i / 16
-- get the second character
SET @s-2 = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1))
+ @s-2
-- build string of hex characters
END
RETURN(@h)
END
GO
CREATE FUNCTION [dbo].[uf_IsSQLAgentJobRunning] (
@SQLAgentJobNameOrId VARCHAR (128))
RETURNS BIT
AS
BEGIN
/* use
dbo.uf_IsSQLAgentJobRunning ('Job name');
dbo.uf_IsSQLAgentJobRunning (0x91527ABA8FC12F4CA896687736BFAB23);
dbo.uf_IsSQLAgentJobRunning ('BA7A5291-C18F-4C2F-A896-687736BFAB23');
*/
DECLARE
@rtn BIT,
@JobName NVARCHAR (128),
@JobHexString NCHAR (34);
-- assume job is not running; must prove otherwise!
SET @rtn = 0;
-- scrub inputs
SET @SQLAgentJobNameOrId = LTRIM (RTRIM (@SQLAgentJobNameOrId));
IF (LEN (@SQLAgentJobNameOrId) = 0)
BEGIN
SET @SQLAgentJobNameOrId = NULL;
END
-- return NULL if NULL or blanks passed (sanity check)
IF @SQLAgentJobNameOrId IS NULL
RETURN NULL;
-- trying to get job name
WITH GetJobInfo (JobName, JobHexString)
AS (SELECT JobName,
master.dbo.uf_HexToChar (job_id, 16) AS JobHexString
FROM master.dbo.v_jobs
WHERE JobName = @SQLAgentJobNameOrId
UNION
SELECT JobName,
master.dbo.uf_HexToChar (job_id, 16) AS JobHexString
FROM master.dbo.v_jobs
WHERE job_id_as_Text = @SQLAgentJobNameOrId
UNION
SELECT JobName,
master.dbo.uf_HexToChar (job_id, 16) AS JobHexString
FROM master.dbo.v_jobs
WHERE job_id_as_bin = @SQLAgentJobNameOrId)
SELECT @JobName = ji.JobName,
@JobHexString = ji.JobHexString
FROM GetJobInfo ji
-- if no matching job found by job_id or name, return NULL
IF @JobName IS NULL
RETURN NULL
-- if both msdb and dm_exec_sessions considers the job as "running", return 1
IF EXISTS
(SELECT 1
FROM sys.dm_exec_sessions sp
WHERE sp.Status IN ('running', 'sleeping')
AND SUBSTRING (sp.[program_name], 32, 32) =
@JobHexString)
AND EXISTS
(SELECT 1
FROM msdb..sysjobs j
WHERE j.name = @JobName
AND EXISTS
(SELECT 1
FROM msdb..sysjobsteps sj
INNER JOIN
msdb..sysjobactivity ja
ON ja.job_id = j.job_id
WHERE sj.job_id = j.job_id
AND ja.start_execution_date <=
GETDATE ()
AND ja.stop_execution_date IS NULL
AND ja.session_id =
(SELECT MAX (ja2.session_id)
FROM msdb..sysjobactivity ja2
WHERE ja2.job_id = j.job_id)))
SET @rtn = 1
RETURN @rtn
END
GO