Obtain Job Name from sp_who2
Published: 10th February 2014
This is a personal pet hate of mine and of many people I’ve spoken to… we have all these lovely tools to obtain a list of what’s happening on our SQL Server but invariably sp_who, sp_who2, and sysprocesses all find it highly entertaining to provide you with a seemingly encrypted result instead of a job name.

So what happens when you encounter something like this in your output:

“SQLAgent - TSQL JobStep (Job 0xB2525BC0DBA1E844B582EFB55131EACF : Step 1)”

Well it’s actually not that hard to convert back into English, it’s just a matter of using the right function.

Basically all you need to do is to strip out the varbinary looking part and run it through the built in varbinary to hex convertor function.

This is the code I use, there may well be better out there, but this is what I came up with at least:

declare @job varchar(100), @jobID varchar(100)

select @job = 'SQLAgent - TSQL JobStep (Job 0xB2525BC0DBA1E844B582EFB55131EACF : Step 1)'

set @job = replace(@job, '(Job 0x', '(0x')

set @jobID = replace(@job, 'SQLAgent - TSQL JobStep (', '')

if (select charindex(' ', @jobID, 1)) > 0
@jobID = substring(@jobID, 1, charindex(' ', @jobID, 1)-1)

replace(@job, @jobID,
select distinct name
from msdb.dbo.sysjobs
where master.dbo.fn_varbintohexstr(job_id) = @jobID

Simple as that. You could easily turn this into a function and use it in conjunction with sysprocesses to make sure that you always have fully readable output.
Allen Yu
20/06/2017 12:21:00
after sort through so many BS, I finally find this real solution, thank you for sharing!
NB: Comments will only appear once they have been moderated.