SQL AGENT 에서 Application 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

에이전트 로그에서

SELECT *
FROM msdb.dbo.sysjobs
WHERE
job_id = dbo.GetJobIdFromProgramName ('SQLAgent - TSQL JobStep (Job 0xC8FAC217480CAB478E3BB07AB02962E9 : Step 2) ') 

같이 검색하면 짜잔~~! 나옴.

이거 땜시로 오늘 완전 개고생 함..