Email Failure Notification with proper/customised failure details

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 382
We can use the below code snippet for emailing job failure notification with proper error message, on which step it got failed, how much time the job ran, and Run date and time....

DECLARE  @RunTime VARCHAR(30) , @Duration VARCHAR(30), @Step VARCHAR(10), @JobName VARCHAR(100), @StepName VARCHAR(100), @Message VARCHAR(1050), @ErrorMessage NVARCHAR(MAX) = ''

SELECT TOP 1 @RunTime = 'RunTime= '+convert(char(10),convert(datetime,convert(char(8),h.run_date)),120) + ' ' + left(right('00000' + cast(run_time as varchar(6)),6),2) + ':' + left(right('00000' + cast(run_time as varchar(6)),4),2) + ':' + right(cast(run_time as varchar(6)),2) + ' ',
@Duration = 'Duration= '+case when (h.run_duration > 1800) then '>' else ' ' end + left(right(convert(char(19),(dateadd(ss,h.run_duration,'')),20),8),8) + ' ',
@Step = 'Step= '+convert(char(3),h.step_id),
@JobName = 'Job Name= '+ left(s.name,50),
@StepName = 'Step Name= '+ left(h.step_name,35),
@Message = 'Message= '+ left(h.message,500)
FROM msdb.dbo.sysjobhistory h
RIGHT JOIN msdb.dbo.sysjobs s on s.job_id = h.job_id
WHERE h.run_status = 0 and s.name = 'Study-Backup' and step_id!=0
ORDER BY h.run_date desc, h.run_time desc, h.step_id

SELECT @ErrorMessage = @JobName + CHAR(13) + @Step + CHAR(13) + @StepName + CHAR(13) + @RunTime + CHAR(13) + @Duration + CHAR(13) + @Message;


execute msdb..sp_send_dbmail
@profile_name = 'DBMailProfileName',
@recipients = 'mailAddress@domain.com',
@subject = 'JOB Name Failure',
@body = @ErrorMessage

Comments or Responses

Login to post response