Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.