Decrypting program name from sysprocesses for agent job

Question

Post reply

Decrypting program name from sysprocesses for agent job

Adam Bean

One Orange Chip

Points: 26631
More actions
June 12, 2008 at 12:41 pm

#121960

Hey all,
Are there any known ways to decrypt the hex shown in sysprocesses on program name when the process is a sql agent job?
For example, I have a job running now and it shows up as: SQLAgent - TSQL JobStep (Job 0x3E31DB24ED72CD49A8114462A66A0367 : Step 1). I want to display the actual job name in place of the hex. Using fn_hex_to_char works:
SELECT
name
,job_id
,'0x' + dbo.fn_hex_to_char(job_id,16)
FROM msdb.dbo.sysjobs
... but I'd prefer to have a function that actually replaced the passed in hex with the appropriate char value.
Thanks

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

Adam Bean

One Orange Chip

Points: 26631

June 13, 2008 at 8:02 am

#828520

Anyone?

akshay1974 SSC-Addicted Points: 444 More actions · Answer 2

The hex is the job_id for the job. You can query sysjobs and it will give you the name of the job.

e.g.

select * from msdb..sysjobs

where job_id = 0x1292021D3C929A4CBBE3895A61FA68CC

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

akshay1974 (6/13/2008)
The hex is the job_id for the job. You can query sysjobs and it will give you the name of the job.
e.g.
select * from msdb..sysjobs
where job_id = 0x1292021D3C929A4CBBE3895A61FA68CC

Negative, the program_name from sysprocesses does not match up to the job_id from sysjobs.

For example, I'm showing SQLAgent - TSQL JobStep (Job 0x3E31DB24ED72CD49A8114462A66A0367 : Step 1) in sysprocesses, yet the job_id for this job is 24DB313E-72ED-49CD-A811-4462A66A0367.

noeld SSC Guru Points: 96590 More actions · Answer 4

Adam Bean (6/13/2008)
akshay1974 (6/13/2008)
The hex is the job_id for the job. You can query sysjobs and it will give you the name of the job.
e.g.
select * from msdb..sysjobs
where job_id = 0x1292021D3C929A4CBBE3895A61FA68CC
Negative, the program_name from sysprocesses does not match up to the job_id from sysjobs.
For example, I'm showing SQLAgent - TSQL JobStep (Job 0x3E31DB24ED72CD49A8114462A66A0367 : Step 1) in sysprocesses, yet the job_id for this job is 24DB313E-72ED-49CD-A811-4462A66A0367.

select j.name

from msdb.dbo.sysjobs j

inner join

msdb.dbo.sysjobsteps s on j.job_id = s.job_id

where step_uid = '3E31DB24ED72CD49A8114462A66A0367' --job step id

* Noel

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

Not the same number there either.

step_uid = 55060C17-0964-459C-A338-9ADF1C40384F

sysprocesses program_name = 0x3E31DB24ED72CD49A8114462A66A0367

noeld SSC Guru Points: 96590 More actions · Answer 6

From your Post:

For example, I'm showing SQLAgent - TSQL JobStep (Job 0x3E31DB24ED72CD49A8114462A66A0367 : Step 1) in sysprocesses, yet the job_id for this job is 24DB313E-72ED-49CD-A811-4462A66A0367.

IF you replace 0x3E31DB24ED72CD49A8114462A66A0367 in the query I posted

for '3E31DB24ED72CD49A8114462A66A0367'

it will show the running jobname

--- Note: NO 0x, single-quoted AND NO "-".

* Noel

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

Not sure If I'm following you here ...

sysjobs:

- job_id = 24DB313E-72ED-49CD-A811-4462A66A0367

sysjobsteps:

- step_uid = 55060C17-0964-459C-A338-9ADF1C40384F

sysprocesses:

- program_name = 0x3E31DB24ED72CD49A8114462A66A0367

How can I get that program_name to match up to the job or the jobstep? The number does not match ... regardless to as of the prefix 0x. The program_name and job_id are similar towards the end, but that's about it.

akshay1974 SSC-Addicted Points: 444 More actions · Answer 8

The hex is the job_id. Try the below

select * from msdb..sysjobs where job_id =

0x3E31DB24ED72CD49A8114462A66A0367

Adam Bean One Orange Chip Points: 26631 More actions · Answer 9

akshay1974 (6/13/2008)
The hex is the job_id. Try the below
select * from msdb..sysjobs where job_id =
0x3E31DB24ED72CD49A8114462A66A0367

Woah, how is this possible? How can I convert that number to match the job_id?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 10

Also, do you know if the hex # in sysprocesses is always the same length? I am trying to figure out the best way to parse out the number to join it ...

akshay1974 SSC-Addicted Points: 444 More actions · Answer 11

Here you go.

declare @jobid varchar(1000)

Declare curtemp cursor for SELECT SUBSTRING (

PROGRAM_NAME ,

PATINDEX ('%(Job %', PROGRAM_nAME)+ 5,

34)

FROM MASTER..SYSPROCESSES WHERE PROGRAM_NAME LIKE '%STEP%'

open curtemp

fetch next from curtemp into @jobid

while @@fetch_status = 0

begin

declare @sql varchar (1000)

select @sql = ' select * from msdb..sysjobs where job_id = ' + @jobid

EXEC (@SQL)

fetch next from curtemp into @jobid

end

close curtemp

deallocate curtemp

akshay1974 SSC-Addicted Points: 444 More actions · Answer 12

Yes, the hex is 32 + 2 for 0x. Its the uniqueidentifier converted in binary format.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 13

Awesome ... but I need to get this into a view, no dynamic sql ...

I'm close, but having some serious issues when converting char to uniqueident ...

This is what I got so far:

SELECT

p.[JobId]

,j.[name]

FROM

(

SELECT

--SUBSTRING(p.[program_name],CHARINDEX('0x',p.[program_name]) +2, CHARINDEX(SPACE(1), p.[program_name], CHARINDEX('0x', p.[program_name])) - CHARINDEX('0x', p.[program_name]) -2) AS [JobId]

CAST(SUBSTRING(p.[program_name],PATINDEX('%(Job %', p.[program_name]) + 5,34) AS UNIQUEIDENTIFIER) AS [JobId]

,p.[program_name]

FROM master.sys.sysprocesses p

WHERE p.[program_name] LIKE 'SQLAgent%JobStep%'

) AS p

INNER JOIN msdb.dbo.sysjobs j

ON j.job_id = p.JobId

Adam Bean One Orange Chip Points: 26631 More actions · Answer 14

Adam Bean

One Orange Chip

Points: 26631

June 16, 2008 at 9:03 am

#829267

Haven't had much progress ...