The below is the query used to send email along with failure notification
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 = @ErrorM
essage
Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif
Bandi, if this helps please login to Mark As Answer. | Alert Moderator