IF (SELECT COUNT(*)
FROM dbo.sysjobs AS s
INNER JOIN sysjobsteps AS s2 (nolock) ON s2.job_id = s.job_id
INNER JOIN dbo.sysjobhistory sh (nolock) ON sh.instance_id=(SELECT MAX(sjh.instance_id) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=s2.step_id)
AND sh.run_date>=(SELECT MAX(sjh.run_date) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=0)
WHERE s.[enabled]=1 AND sh.run_status=0)>0
BEGIN
DECLARE @tableHTML AS NVARCHAR(max)
SET @tableHTML =
N'<H1>Ошибки в джобах на sql сервере '+@@SERVERNAME+'</H1>' +
N'<table border="1">' +
N'<tr><th>Джоб</th><th>ID шага</th>' +
N'<th>Имя шага</th><th>команда</th><th>база</th>' +
N'<th>последний запуск</th><th>ошибка</th></tr>' +
CAST ( ( SELECT td = s.name,'', td = s2.step_id,'', td = s2.step_name, '',td = cast(s2.command AS VARCHAR(100)),'', td = s2.database_name,'',
td = s2.last_run_date,'', td = cast(sh.[message] AS VARCHAR(100))
FROM sysjobs AS s (nolock)
INNER JOIN sysjobsteps AS s2 (nolock) ON s2.job_id = s.job_id
INNER JOIN dbo.sysjobhistory sh (nolock) ON sh.instance_id=(SELECT MAX(sjh.instance_id) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=s2.step_id)
AND sh.run_date>=(SELECT MAX(sjh.run_date) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=0)
WHERE s.[enabled]=1 AND sh.run_status=0
--ORDER BY s.name, s2.step_id
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
DECLARE @subject AS NVARCHAR(max)
SET @subject='Ошибка в работе джобов на сервере '+@@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'atrans'
,@recipients = 'mail'
,@subject = @subject
,@importance ='Normal'
,@body = @tableHTML
,@body_format = 'HTML'
END
Similar Posts: