Job history emails

One of my crime fighting cohorts had a request to provide some additional details, than that you can normally get via SSMS when a job fails. So the below script is what we ended up throwing together.  It makes use of the msdb database, but allows us to pick and choose which columns in the sysjobs and sysjobhistory tables had value to us.

Script:

   1: DECLARE    @BodyHtml NVARCHAR(MAX);

   2: SET        @BodyHtml =    N'Hello, <br /><br />

   3:                           An error has occured attempting to run the Emails orders job<br />'+

   4:                           N'For the last 5 failed executions of this SQL Agent job, please see the table below:<br />'+

   5:                           N'<table border="1">' +

   6:                                           N'<tr><th>Step ID</th><th>Step Name</th><th>Message</th><th>DateRun</th></tr>' +

   7:                                           CAST ( (  SELECT TOP 5     td = t1.step_id,         '',

   8:                                                                      td = t1.step_name,       '',

   9:                                                                      td = t1.[message],       '',

  10:                                                                      td = t1.run_date

  11:                                                     FROM             msdb.dbo.sysjobhistory t1

  12:                                                     LEFT JOIN        msdb.dbo.sysjobs t2

  13:                                                                      on t1.job_id = t2.job_id

  14:                                                     WHERE            t2.name='Email orders'                                

  15:                                                     AND              t1.run_status ='0'

  16:                                                     AND              t1.step_id <> '0'

  17:                                                     ORDER BY         t1.run_date ,t1.run_time desc

  18:                                                     FOR XML PATH('tr'), TYPE 

  19:                                                     ) AS NVARCHAR(MAX) ) +

  20:                           N'</table><br /><br />'+

  21:                           N'<br />Thank you,<br />

  22:                           Adventure Works IT' ;      

  23:  

  24: EXEC msdb.dbo.sp_send_dbmail 

  25: @profile_name = 'AW_Admin',

  26: @recipients = 'SQLServerNotification@AdventureWorks.co.uk',

  27: @subject = 'Emai orders job failed - live',

  28: @body_format = 'HTML',

  29: @Body = @BodyHtml,

  30: @importance = 'high'

Result:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s