How to find the session id of running jobs

Question

Trying to get the session id of executing jobs

;with JobDetails as
(
select Job_Id = left(intr1,charindex(':',intr1)-1),
       Step = substring(intr1,charindex(':',intr1)+1,charindex(')',intr1)-charindex(':',intr1)-1),
       SessionId = spid
    from master.dbo.sysprocesses x 
    cross apply (select replace(x.program_name,'SQLAgent - TSQL JobStep (Job ','')) cs (intr1)
    where spid > 50 and x.program_name like 'SQLAgent - TSQL JobStep (Job %'
)
select * 
    from msdb.dbo.sysjobs j 
    inner join JobDetails jd on j.job_id = jd.Job_Id

but it throws the following error

Msg 8169, Level 16, State 2, Line 47 Conversion failed when converting from a character string to uniqueidentifier.

I tried to cast the job_id to varbinary but it produces no result

;with JobDetails as
(
select Job_Id = left(intr1,charindex(':',intr1)-1),
       Step = substring(intr1,charindex(':',intr1)+1,charindex(')',intr1)-charindex(':',intr1)-1),
       SessionId = spid
    from master.dbo.sysprocesses x 
    cross apply (select replace(x.program_name,'SQLAgent - TSQL JobStep (Job ','')) cs (intr1)
    where spid > 50 and x.program_name like 'SQLAgent - TSQL JobStep (Job %'
)
select * 
    from msdb.dbo.sysjobs j 
    inner join JobDetails jd on cast(j.job_id as varbinary) = jd.Job_Id

but when I copy paste a job_id from cte like this

select job_id, name 
    from msdb..sysjobs 
    where job_id = 0x128A47A31EAB8F4DA1AD852093D815F5 

it works. Any idea how to fix this query

Answer 1

I used CONVERT to convert the msdb.dbo.sysjob.job_id to BINARY(16) and then used CONVERT (and a style) to convert that to a varchar(max) which could be compared to what comes out of your cte. You can read more about CONVERT and styles by reviewing the documentation for CONVERT. This worked in my testing.

Try this:

;
WITH JobDetails
AS (
    SELECT DISTINCT Job_Id = left(intr1, charindex(':', intr1) - 1)
        ,Step = substring(intr1, charindex(':', intr1) + 1, charindex(')', intr1) - charindex(':', intr1) - 1)
        ,SessionId = spid
    FROM master.dbo.sysprocesses x
    CROSS APPLY (
        SELECT replace(x.program_name, 'SQLAgent - TSQL JobStep (Job ', '')
        ) cs(intr1)
    WHERE spid > 50
        AND x.program_name LIKE 'SQLAgent - TSQL JobStep (Job %'
    )
SELECT *
FROM msdb.dbo.sysjobs j
INNER JOIN JobDetails jd ON jd.Job_Id = convert(VARCHAR(max), convert(BINARY (16), j.job_id), 1)