[MSSQL]Job ID 找尋 Job Name

來源資料是:
‘SQLAgent – TSQL JobStep (Job 0x8A03F610E9E74D499AB059B40F9E4D89 : Step 1)’
但是上面的一堆碼怎麼對應到SQL Server Agnet 內的作業名稱呢?

可以使用下面語法:

--宣告來源資料
declare @program_name nvarchar(128)='SQLAgent - TSQL JobStep (Job 0x8A03F610E9E74D499AB059B40F9E4D89 : Step 1)'
--找尋資料開始位置
DECLARE @start_of_job_id int
SET @start_of_job_id = CHARINDEX('(Job 0x', @program_name) + 7
--宣告對應job_id的uniqueidentifier
Declare @GetJobIdFromProgramName as uniqueidentifier
--來源資料轉換job_id
set @GetJobIdFromProgramName=  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)
--查詢job相關資訊
SELECT * FROM msdb.dbo.sysjobs WHERE job_id =@GetJobIdFromProgramName

參考資料:
Identifying SQL Agent Job Name based on the job id.
CHARINDEX (Transact-SQL)

作者: EmptyWu

十年左右的攻城經歷,但始終覺得能力不足,希望這地方可以填滿不足點。

有任何問題,歡迎提問~