JOB Step Failure Message [Resolved]

Posted by Bandi under Sql Server on 6/25/2014 | Points: 10 | Views : 502 | Status : [Member] [MVP] | Replies : 2
I Would like to mail the Step failure message of a job to an Operator/EMail Address..

By using SQL Server Agent i can able to send email notification for the job failure. But i wanted to send the actual error message of Step/Job failure message along with the mail notification. How to do this?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif



Responses

Posted by: Bandi on: 6/25/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down

Resolved
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

Posted by: Bandi on: 6/25/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
you are welcome to post any alternate solution for the above requirement

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

Login to post response