SSRS Subscription: Send Email on Report Failure

Question

I have an SSRS report with some pre-selected parameters.

Every once in a while, a report parameter will randomly blank itself out and the subscription won't create the file.

Is there a way I can be emailed in the event that the server can't create the report?

Answer 1

I faced a similar problem, I ended up by creating configuring the Database Mail and creating an Operator to notifies me when the underlying job of the subscription fails.

1. Configure Database Mail to send a mail through a SMTP server even gmail or outlook works.
2. Create an Operator, Operators are aliases for people or groups that can receive electronic notification when jobs have completed or alerts have been raised.

3. Identify the underlying job that runs your subscription, use this query against the ReportServer database.

SELECT c.Name AS ReportName , rs.ScheduleID AS JOB_NAME FROM dbo.Catalog c JOIN dbo.Subscriptions s ON c.ItemID = s.Report_OID JOIN dbo.ReportSchedule rs ON c.ItemID = rs.ReportID AND rs.SubscriptionID = s.SubscriptionID

The JOB_NAME column will give you the name of the job.

4. SQL Server Agent via SSMS go to the Jobs folder look for the job and right click it to go to properties.
5. In the Notifications tab set the Operator created before and the condition to send the mail.