The function below will return the job_id when you pass in the string with the "Job 0x...." (I've since discovered a perhaps better way, but can't find that code right now).
You can use msdb.dbo.sysjobs to translate the job_id to a job_name.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetJobIdFromProgramName] (
@program_name nvarchar(128)
)
RETURNS uniqueidentifier
AS
BEGIN
DECLARE @start_of_job_id int
SET @start_of_job_id = CHARINDEX('(Job 0x', @program_name) + 7
RETURN CASE WHEN @start_of_job_id > 0 THEN CAST(
SUBSTRING(@program_name, @start_of_job_id + 06, 2) + SUBSTRING(@program_name, @start_of_job_id + 04, 2) +
SUBSTRING(@program_name, @start_of_job_id + 02, 2) + SUBSTRING(@program_name, @start_of_job_id + 00, 2) + '-' +
SUBSTRING(@program_name, @start_of_job_id + 10, 2) + SUBSTRING(@program_name, @start_of_job_id + 08, 2) + '-' +
SUBSTRING(@program_name, @start_of_job_id + 14, 2) + SUBSTRING(@program_name, @start_of_job_id + 12, 2) + '-' +
SUBSTRING(@program_name, @start_of_job_id + 16, 4) + '-' +
SUBSTRING(@program_name, @start_of_job_id + 20,12) AS uniqueidentifier)
ELSE NULL
END
END --FUNCTION
Sample usage:
SELECT *
FROM msdb.dbo.sysjobs
WHERE
job_id = dbo.GetJobIdFromProgramName ('SQLAgent - TSQL JobStep (Job 0xFB668E27919DA3489E3DD97061F25B31 : Step 1) ')
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".