[스크립트] 배치잡 실패 수집

다양한 서비스 DB가 구동중인 통합 SQL Server에서 서비스 담당자별 배치잡 실패 메일링을 하기 위해 작성되었습니다.
해당 쿼리는 30분 단위로 배치잡 실패 이력을 수집 및 발송하는 것이 목표입니다. 
제공 데이터는 [배치명/스탭/에러문구/발생시간/수행기간(elapsedTime)/다음수행시간] 입니다.

실행권한

grant execute on msdb.dbo.agent_datetime to [Account] grant select on [msdb].[dbo].[sysjobhistory] to [Account] grant select on [msdb].[dbo].[sysjobschedules] to [Account] grant select on [msdb].[dbo].[sysjobs] to [Account]

쿼리문

--by jyoh .200900   
DECLARE @Param_date1 varchar(16) 
set @Param_date1 = convert(varchar(16),dateadd(minute,-30,getdate()),120) 
 
 
( SELECT  JobL.name as [배치명], JobH.step_name as [스탭단계], 
		 REPLACE(message, '. ', '.' + CHAR(10)) as [메시지] , msdb.dbo.agent_datetime([JobH].run_date,[JobH].run_time) as [실패시간],   
		CASE WHEN LEN([JobH].run_duration ) IN (1,2) THEN Right([JobH].run_duration ,2)+'초'
			WHEN LEN([JobH].run_duration ) = 3 THEN 
						SUBSTRING(cast([JobH].run_duration  as varchar),1,1) + '분 ' + Right([JobH].run_duration ,2) + '초'
			WHEN LEN([JobH].run_duration ) = 4 THEN
						SUBSTRING(cast([JobH].run_duration  as varchar),1,2) + '분 ' + Right([JobH].run_duration ,2) + '초'
			WHEN LEN([JobH].run_duration ) = 5 THEN 
						SUBSTRING(cast([JobH].run_duration  as varchar),1,1) + '시 ' +
						SUBSTRING(cast([JobH].run_duration  as varchar),2,2) + '분 ' + 
						Right([JobH].run_duration ,2) + '초'
			WHEN LEN([JobH].run_duration ) = 6 THEN
						SUBSTRING(cast([JobH].run_duration  as varchar),1,2) + '시 ' +
						SUBSTRING(cast([JobH].run_duration  as varchar),3,2) + '분 ' + 
						Right([JobH].run_duration ,2) + '초'
		end as [수행시간],
		 msdb.dbo.agent_datetime([JobS].next_run_date,[JobS].next_run_time) as [다음 일정]
	  FROM  [msdb].dbo.sysjobhistory AS [JobH] WITH(NOLOCK)
	  left join msdb.dbo.sysjobs   as [JobL] WITH (NOLOCK)
	  on JobH.job_id = JobL.job_id
	  left join msdb.dbo.[sysjobschedules] as [JobS] with (NOLOCK)
	  on JobH.job_id = JobS.job_id
	  WHERE  1=1  
		and [JobH].run_status =0  and [JobH].step_id > 0
		and [JobS].next_run_date !=0
		-- and JobL.name in ('') --타겟 배치명 입력
		and  CAST(CONVERT(VARCHAR, [JobH].run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, [JobH].run_time),6),5,0,':'),3,0,':') AS DATETIME) > @Param_date1
	)