Is it possible to set up an alert in SQL Server 2008 that will send an e-mail anytime a job in a specific category fails?
I am wondering because I would like to set up an e-mail anytime an SSRS subscription fails – and all of these subscriptions are jobs in the category Report Server.
EDIT – it turns out that when an SSRS subscription fails, the job itself does not fail, so my question will not apply to the SSRS subscription monitoring use. However I would still like to know for other jobs that we run in our environment
Best Answer
You could create a job that checks the msdb.dbo.sysjobhistory table every minute (or however frequently you want). You might want to implement a queue table so you only ever send the message for any single instance failure once.
So then your code, that you can schedule in a job, becomes:
Now I assume you want to send an individual e-mail for each failure, so this could be part of the job as well (or part of a different job, though that isn't necessarily wise):
There are some other options too:
If Database Mail isn't already set up, please see this tutorial.
You could also use 3rd party tools (e.g. SQL Sentry) that will make a lot of this simpler. Full disclosure: I work for SQL Sentry.