asked on

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?

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MicroAutomation

ASKER

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?

chapmandew

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.