June 16, 2008 at 11:56 am
The thing that is really killing me is that if you cast the job_id from sysjobs as varbinary, it looks identical to the program_name in sysprocesses …
SELECT program_name FROM sys.sysprocesses
WHERE SPID = 55
SQLAgent - TSQL JobStep (Job 0x3E31DB24ED72CD49A8114462A66A0367 : Step 1)
SELECT CAST(job_id AS VARBINARY)
FROM msdb.dbo.sysjobs
WHERE name = 'looptest'
sysprocesses = 0x3E31DB24ED72CD49A8114462A66A0367
sysjobs = 0x3E31DB24ED72CD49A8114462A66A0367
Yet even If I cast my string (( CAST(SUBSTRING(p.[program_name],PATINDEX('%(Job %', p.[program_name]) + 5,34) AS VARBINARY) AS [JobId] )) as VARBINARY, the results don't match on the join.
AH!
June 16, 2008 at 12:14 pm
Can anyone think of a better way to do the join? It’s not pretty, but this appears to be working …
SELECT
CASE
WHEN p.program_name LIKE 'SQLAgent - TSQL JobStep%' THEN 'Job: ' + SUBSTRING(j.name,1,30)
ELSE SUBSTRING(p.[program_name],1,35)
END
FROM master.sys.sysprocesses p
LEFT JOIN msdb.dbo.sysjobs j
ON SUBSTRING(ISNULL(p.[program_name],''),CHARINDEX('0x', ISNULL(p.[program_name],'')) + 18, 16) = SUBSTRING(REPLACE(ISNULL(j.[job_id],''), '-',''),17,16)
June 13, 2012 at 11:36 am
Please see if below (done in 2008R2) converts program_name column into matching job_id. If it works you can further converted it into function.
DECLARE @program_name nvarchar(200)
,@job_id_str nvarchar(50);
SET @program_name = 'SQLAgent - TSQL JobStep (Job 0x89BDB29BF35F664EA1AF77958B95F77C : Step 1)';
SET @job_id_str = SUBSTRING(@program_name, 32, 32);
SELECT SUBSTRING(@job_id_str,7,2) +
SUBSTRING(@job_id_str,5,2) +
SUBSTRING(@job_id_str,3,2) +
SUBSTRING(@job_id_str,1,2) +
'-' +
SUBSTRING(@job_id_str,11,2) +
SUBSTRING(@job_id_str,9,2) +
'-' +
SUBSTRING(@job_id_str,15,2) +
SUBSTRING(@job_id_str,13,2) +
'-' +
SUBSTRING(@job_id_str,17,4) +
'-' +
SUBSTRING(@job_id_str,21,12) ;
August 1, 2012 at 4:11 am
This works:
declare @prog_name nvarchar(500), @job_id varbinary(32);
select --*
top 1 @prog_name = program_name
from sysprocesses
where program_name like 'SQLAgent - TSQL JobStep %';
--OR
--set @prog_name = 'SQLAgent - TSQL JobStep (Job 0xD03E45C412E25C4CB7862EB7762F5023 : Step 3)'
--debug
--select substring(@prog_name, 30, 34) as sub_prog_name
--convert from string to varbinary
select @job_id = convert(varbinary(32), substring(@prog_name, 30, 34), 1) -- NB CONVERT style 1!
--debug
--select @job_id as job_id;
select name --, job_id
from msdb..sysjobs
where job_id = @job_id
But you couldn't do a general view, even with a left join, using that as the join clause, like the above:
select
spid,
case
when p.program_name LIKE 'SQLAgent - TSQL JobStep%' then 'Job: ' + substring(j.name,1,30)
else substring(p.[program_name],1,35)
end
from master.sys.sysprocesses p
left join msdb.dbo.sysjobs j
on (convert(varbinary(32), substring(p.[program_name], 30, 34), 1) = j.job_id)
unless you limited it with a where clause e.g. when p.program_name LIKE 'SQLAgent - TSQL JobStep%', to avoid a conversion error. You'd be best off doing a union.
e.g.
select
spid,
'Job: ' + substring(j.name,1,50),
hostname,
loginame
from master.sys.sysprocesses p
left join msdb.dbo.sysjobs j
on (convert(varbinary(32), substring(p.[program_name], 30, 34), 1) = j.job_id)
where p.program_name like 'SQLAgent - TSQL JobStep%'
union
select
spid,
substring(isnull(p.[program_name], ''), 1, 55),
hostname,
loginame
from master.sys.sysprocesses p
where isnull(p.program_name, '') not like 'SQLAgent - TSQL JobStep%'
and spid > 50
order by 2, 1
December 19, 2012 at 12:48 am
[font="Times New Roman"][/font]
Thank u thanks a lot it worked for me
December 26, 2013 at 1:48 am
Better give this one a try though an undocumented function is being used here:)
SELECT p.spid, j.name As 'Agent Job Name',*
FROM master.dbo.sysprocesses p
JOIN msdb.dbo.sysjobs j ON
master.dbo.fn_varbintohexstr(convert(varbinary(16), job_id)) COLLATE Latin1_General_CI_AI =
substring(replace(program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34)
where spid>50
HTH!
Manu
October 24, 2018 at 7:44 am
Try below script:
declare @jobhexid varchar(100)
set @jobhexid ='SQLAgent - TSQL JobStep (Job 0xD03E45C412E25C4CB7862EB7762F5023 : Step 3'
select * FROM [msdb].[dbo].[sysjobs]
WHERE CONVERT(NVARCHAR(MAX), CONVERT(VARBINARY, job_id), 1) = SUBSTRING(@jobhexid,CHARINDEX('0x', @jobhexid), CHARINDEX(' ', @jobhexid, CHARINDEX('0x', @jobhexid))-CHARINDEX('0x', @jobhexid))
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy