Notification if job completes with errors

Question

I have a job that runs a stored procedure to update SQL tables; it then deletes and updates tables in Access. I have found that sometimes this job seems to "fail" somewhere in executing (end result is that Access tables are not updated), yet the notification that I receive indicates "the job succeeded." Is there a way to set up notifications if the job completes, but there are errors in any of the steps?

Answer 1

Hi Charlie725,

Could you please post the detailed error message you got?

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

Hi Peja,

The job confirmation is that it succeeds. However when the job runs, it may have "completed with errors" - is there a way to find that kind of error detail, and more importantly, is it possible to send notifications when that occurs (success/completed, but with errors)?

Thanks very much!

Answer 3

@charlie

I think you can try running a query to find those  jobs and then create a CLR stored procedure to send notification mail.

You may refer to:

1)How to: Notify an Operator of Job Status (SQL Server Management Studio)
 http://msdn.microsoft.com/en-us/library/ms191130.aspx
 
2)Job Properties / New Job (Notifications Page)
 http://msdn.microsoft.com/en-us/library/ms189685.aspx
 
3) http://sqlserverpedia.com/wiki/SQL_Server_Agent_Job_Query_Samples
 
4)http://geekswithblogs.net/rajap/archive/2009/08/04/implementing-sql-clr-triggers.aspx

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.