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 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply