Obtain Job Name from sp_who2
Published: Jul 03, 2015
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
begin
    set
@jobID = substring(@jobID, 1, charindex(' ', @jobID, 1)-1)
end

select
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron