• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

How do I get job failure notification to attach the output of the SQL query?

I am using the Job Notification features of SQL Server 2005 to periodically scan certain database tables for records containing bad return codes.  When bad return codes are detected, the SQL Server job uses the RAISERROR command to trigger an error severe enough to cause the job to fail.  When the job failure occurs, the notification is setup to send an e-mail to a specific e-mail address.  I would like the output of the job (i.e. SQL query) to be attached to the notification e-mail or available in the text body.  Is there any way to accomplish what I would like to do using SQL Server 2005?
0
MicroAutomation
Asked:
MicroAutomation
  • 2
1 Solution
 
chapmandewCommented:
Yes....database mail can be used for this.

http://articles.techrepublic.com.com/5100-10878_11-6164310.html
0
 
MicroAutomationAuthor Commented:
Thanks.  I reviewed the article and understand how it can be done using a stored procedure, but I was hoping to use the native Notification features available with SQL Server Jobs.  Is there no way to add attachments to a Job Notification?  Or, do I need to do the notification within my SQL Server job using the EXEC msdb.dbo.sp_send_dbmail stored procedure?
 
0
 
chapmandewCommented:
You can setup alerts on jobs for failures, but you'll need to cofigure an operator to do so.  I think it is just easier to add a job step and revert flow to it upon failure.  Can easily control the message you send.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now