I need help, please!
I have a monitoring table that pulls in information about program name (plus sp_who2 does as well), and I need to join it to msdb.dbo.sysjobs to get the job name where the program name starts with SQLAgent (like SQLAgent - TSQL JobStep (Job 0x8FA89775AAF135499FA4CC1621B639FB : Step 1)).
Nothing I try is working. Job name always comes up NULL. I've found the two below links and I cannot get them to work. Below is the latest version of my code. Can anyone help me figure this out?
Identifying SQL Agent Job Name based on the job id. – SQLServerCentral Forums
Running processes including job step name | Robert's technology blog (rbvandenberg.com)
DECLARE @Table TABLE(
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime BIGINT,
DiskIO BIGINT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT);
INSERT INTO @Table (SPID, Status, LOGIN, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName, SPID_1, REQUESTID)
EXEC sp_who2;
SELECT SPID, Status, LOGIN, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName, SPID_1, REQUESTID
FROM @Table
WHERE ProgramName LIKE 'SQLAgent%';
WITH jobs AS (SELECT j.name,CAST(j.job_id AS VARBINARY) as jobid
FROM msdb.dbo.sysjobs j)
SELECT t.SPID, t.Status, t.ProgramName, j.name AS JobName
FROM @Table t
LEFT OUTER JOIN jobs j
ON CONVERT(VARBINARY(32),RTRIM(substring(ProgramName, 30, 34))) = j.jobid;
WITH jobs AS (SELECT j.name,CAST(j.job_id AS VARBINARY) as jobid
FROM msdb.dbo.sysjobs j)
SELECT t.SPID, t.Status, t.ProgramName, j.name AS JobName
FROM @Table t
LEFT OUTER JOIN jobs j
ON substring(ProgramName, 30, 34) = CONVERT(VARCHAR(34),j.jobid);
November 19, 2024 at 3:17 pm
And after changing my search terms in Google to "SQL Server get job name from sp_who2 program_name" instead of something more generic, I HAVE FOUND IT!
CONVERT(VARCHAR(34),master.dbo.fn_VarbinToHexStr(sj.Job_ID))
This function converts jobID to Hex and then I wrapped it in a CONVERT for VARCHAR(34) and joined on that.
DECLARE @Table TABLE(
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime BIGINT,
DiskIO BIGINT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT);
INSERT INTO @Table (SPID, Status, LOGIN, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName, SPID_1, REQUESTID)
EXEC sp_who2;
SELECT SPID, Status, LOGIN, HostName, BlkBy, DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName, SPID_1, REQUESTID
FROM @Table
WHERE ProgramName LIKE 'SQLAgent%';
SELECT t.SPID, t.Status, t.ProgramName, j.name AS JobName
FROM @Table t
LEFT OUTER JOIN msdb.dbo.sysjobs j
ON substring(ProgramName, 30, 34) = CONVERT(VARCHAR(34),master.dbo.fn_VarbinToHexStr(j.Job_ID));
November 19, 2024 at 3:18 pm
Weird gateway error caused repost. And won't let me delete the code block.
November 19, 2024 at 4:03 pm
You could just do a straight select also, no need to convert the hex to the GUID as it will implicitly do this for you
SELECT * FROM msdb.dbo.sysjobs WHERE job_id = 0x8FA89775AAF135499FA4CC1621B639FB
November 19, 2024 at 4:25 pm
You could just do a straight select also, no need to convert the hex to the GUID as it will implicitly do this for you
SELECT * FROM msdb.dbo.sysjobs WHERE job_id = 0x8FA89775AAF135499FA4CC1621B639FB
That's extraordinarily manual. I don't want to do that for every line in the table I have to read when all the lines are different. I've been doing that for 3 months and fighting for this solution to save time.
There isn't really a need to use that scalar function for the conversion.
This will also work:
SELECT t.SPID, t.Status, t.ProgramName, j.name AS JobName
FROM @Table t
LEFT OUTER JOIN msdb.dbo.sysjobs j
ON substring(ProgramName, 30, 34) = CONVERT(VARCHAR(34),CONVERT(VARBINARY,j.Job_ID),1)
WHERE t.ProgramName LIKE 'SQLAgent%(Job%';
The third parameter on the CONVERT to Varchar from Binary does the trick.
November 19, 2024 at 4:40 pm
There isn't really a need to use that scalar function for the conversion.
This will also work:
SELECT t.SPID, t.Status, t.ProgramName, j.name AS JobName
FROM @Table t
LEFT OUTER JOIN msdb.dbo.sysjobs j
ON substring(ProgramName, 30, 34) = CONVERT(VARCHAR(34),CONVERT(VARBINARY,j.Job_ID),1)
WHERE t.ProgramName LIKE 'SQLAgent%(Job%';The third parameter on the CONVERT to Varchar from Binary does the trick.
I was having trouble in SSMS getting that third parameter / conversion to VARBINARY to work based on the articles I previously noted, but I see your code is different from those articles. I will try it out. Thanks.
November 19, 2024 at 4:44 pm
Kaj, that does work faster than the function I found via Google. THANK YOU SO MUCH!
November 19, 2024 at 4:59 pm
Ant-Green wrote:You could just do a straight select also, no need to convert the hex to the GUID as it will implicitly do this for you
SELECT * FROM msdb.dbo.sysjobs WHERE job_id = 0x8FA89775AAF135499FA4CC1621B639FBThat's extraordinarily manual. I don't want to do that for every line in the table I have to read when all the lines are different. I've been doing that for 3 months and fighting for this solution to save time.
Apologies I seem to have miss read the text way it was being converted.
As you have the job hex in varchar from the substring, converting that to the GUID and then joining
SELECT t.SPID, t.Status, t.ProgramName, j.name AS JobName
FROM @Table t
LEFT OUTER JOIN msdb.dbo.sysjobs j
ON CONVERT(UNIQUEIDENTIFIER,substring(ProgramName, 30, 34)) = j.Job_ID;
November 19, 2024 at 5:07 pm
I'm sorry. The post that was previously here was harsh. I am removing it because there was no call for me to sound so short.
Converting to uniqueidentifier did not work for me. I tried that multiple times.
November 19, 2024 at 5:12 pm
Apologies I seem to have miss read the text way it was being converted.
As you have the job hex in varchar from the substring, converting that to the GUID and then joining
SELECT t.SPID, t.Status, t.ProgramName, j.name AS JobName
FROM @Table t
LEFT OUTER JOIN msdb.dbo.sysjobs j
ON CONVERT(UNIQUEIDENTIFIER,substring(ProgramName, 30, 34)) = j.Job_ID;
That might also work, however you missed a conversion step. I believe this will also work:
SELECT t.SPID, t.Status, t.ProgramName, j.name AS JobName
FROM @Table t
LEFT OUTER JOIN msdb.dbo.sysjobs j
ON CONVERT(UNIQUEIDENTIFIER,CONVERT(VARBINARY,SUBSTRING(ProgramName, 30, 34),1)) = j.Job_ID
WHERE t.ProgramName LIKE 'SQLAgent%(Job%';
November 19, 2024 at 5:50 pm
Just a thought...
Depending on your need for data, you might also consider using the system view sys.dm_exec_sessions instead the somewhat unwieldy stored procedure sp_who2 as the basis for your query:
Either
SELECT s.session_id AS SPID, s.Status, s.program_name AS ProgramName, j.name AS JobName
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN msdb.dbo.sysjobs j
ON substring(s.program_name, 30, 34) = CONVERT(VARCHAR(34),CONVERT(VARBINARY,j.Job_ID),1)
WHERE s.program_name LIKE 'SQLAgent%(Job%';
or
SELECT s.session_id AS SPID, s.Status, s.program_name AS ProgramName, j.name AS JobName
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN msdb.dbo.sysjobs j
ON CONVERT(UNIQUEIDENTIFIER,CONVERT(VARBINARY,substring(s.program_name, 30, 34),1)) = j.Job_ID
WHERE s.program_name LIKE 'SQLAgent%(Job%';
November 24, 2024 at 5:32 am
This was removed by the editor as SPAM
November 24, 2024 at 5:33 am
This was removed by the editor as SPAM
December 2, 2024 at 7:20 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply