SSRS Failed Subscription Alerting

Even now, with SQL Server 2016 SP1 released only a few months ago, it baffles me that there is still no built-in functionality to alert admins or users of failed subscriptions in Reporting Services. We still rely on scripts like the one I’m about to describe or report recipients contacting administrators/helpdesks when their report emails don’t arrive or fail to appear in file shares. This is something that people have had to work around for years. There is some documentation that Microsoft provides to help you get started with monitoring subscriptions from the log files with direction on using PowerShell but it is not by any means a complete solution. (Monitor Reporting Services Subscriptions)

Variations on a theme

As you can imagine, with this being a lingering issue in SSRS there have been a number of similar solutions and approaches to handle & troubleshoot failed subscriptions.

One such example is Dean Kalanquin’s blog post on MSDN from 2009 (Monitoring and Troubleshooting Subscriptions) which details how SSRS treats subscriptions with a few examples of why they’d fail. NOTE: Its only related to SQL Server 2005 & 2008 though.

Also there’s still many Active feedback posts with similar workarounds & no solutions. I.e. (Notification of SSRS subscription when it fails).

My Approach

With over 50 subscriptions set up by BI developers and over 100 created by users in a separate SharePoint integrated instance, there are a lot of potential failures that could be missed by both admins & users. This script was put together when we were running SQL Server 2008 & still remains in use through 2008 R2, 2014 & most likely when we upgrade to 2016 too. I have recently made my own improvements & tweaks as a pet project.

This is an example of the script’s output. I’ve kept it fairly simple as it’s used mainly by admins & to keep code maintenance to a minimum. As we are constructing it in HTML you can add any amount of formatting or customisation to suit your requirements.

The SQL script should be setup as a T-SQL step in a SQL Agent job, set to run as often as you require. The majority of my subscriptions go out at the same time each day so I have it running twice a day within a few hours of each other & only highlighting failures in the last 24 hours. This script will capture all SSRS based subscription failures:

Email subscriptions
File Share subscriptions
Data Driven subscriptions
Data source Cache refreshes

NOTE: The 24 hour restriction is good to stop you being spammed regularly by an unfixable error or one that requires further investigation. The downside being that on a rare occasion I’ve found a failure has been missed (on a monthly scheduled report) and only rediscovered months later when the user notified us of 2 months’ worth of reports missing. This is because the original failure would stop subsequent schedule runs from executing & the last run would be beyond the 24 hour window. To get the best of both worlds, you may be best running a “time restricted Agent job” daily and a non-restricted job once a month to capture any unresolved errors.

Upon discovering a failed subscription the script sends an email to the set recipients with all the relevant information, including the SQL Agent job name, (which is a horribly unreadable GUID for every SSRS subscription job). This allows you to re-run the subscription easily & clear the failure error.

Notes

I have excluded a few different status options in this script as they would be considered success scenarios. I may not have caught all possible options as this list was built over time. Feel free to change or adjust to suit. Microsoft provides a list of possible values for the Status column in the documentation I linked to at the start (Monitor Reporting Services Subscriptions) though it doesn’t cover Cache refreshes or subscriptions that may be executing when you query the table (ie. “Pending” status).

WHERE

Sub.[LastStatus] NOT LIKE '%was written%' --File Share subscription

AND Sub.[LastStatus] NOT LIKE '%pending%' --Subscription in progress. No result yet

AND Sub.[LastStatus] NOT LIKE '%mail sent%' --Mail sent successfully.

AND Sub.[LastStatus] NOT LIKE '%New Subscription%' --New Sub. Not been executed yet

AND Sub.[LastStatus] NOT LIKE '%been saved%' --File Share subscription

AND Sub.[LastStatus] NOT LIKE '% 0 errors.' --Data Driven subscription

AND Sub.[LastStatus] NOT LIKE '%succeeded%' --Success! Used in cache refreshes

AND Sub.[LastStatus] NOT LIKE '%successfully saved%' --File Share subscription

AND Sub.[LastStatus] NOT LIKE '%New Cache%' --New cache refresh plan

I have also defined a few specific variables which will need to be changed to suit your own environment.

SELECT @EmailRecipient = 'Changeme@craigporteous.com'

This can be multiple users, simply separate them with a semicolon.

--Set DB Mail profile to use

SELECT TOP 1 @ProfileName = [Name] FROM msdb.dbo.sysmail_profile WHERE [Name] = 'Alert-BI-Admins'

The profile will be the relevant Operator listed under the SQL Server Agent shown in Management Studio.

These are the only necessary changes you will need to make to the script for it to work. You can however, alter the fields returned, as well as the HTML section to include company logos or other formatting. In the case of several reporting instances, I further differentiate them by changing the colour of the header text in the email body by editing this HTML snippet:

N'<H3 style=”color:red; font-family:verdana”>

The main body of the email is created as an HTML table with the dataset placed inside & header’s manually defined. As you can see in the first SELECT statement I’ve added ISNULL to convert null values into zero length strings.

ISNULL(REPLACE(Sub.[Description],'send e-mail to ',''),' ') AS Recipients

This is done as some subscriptions will have no value in the description field. The null causes the remaining columns in that row to move out of line. Its purely a formatting change.

Further Development

Although I’ve opted to keep the output simple, it is possible to add more functionality to the dataset, such as making report paths into clickable links to speed up troubleshooting. This can be achieved by wrapping a link tag with your report server URL around the [Path] field on the first select statement then casting as XML within the table select statement.

Eg.

‘<a href=“http://MyReportingSite.com/Reports/Pages/Report.aspx?ItemPath=’ + Cat.[Path] + ‘ “></a>’

It can be tricky if you are using special characters in report names or folders. This can break the XML conversion and cause the whole process to fail. Wrapping the field in a REPLACE would capture the most common character though

td =CAST(REPLACE(t.[Path], ‘&’, ‘&amp’) AS XML),'',

Failed Subscription Alerting T-SQL Script

This is a standardised version of the entire script that should be added as a T-SQL step to a SQL Agent job. I’ve commented out the date restriction that I mentioned above.

100

USE ReportServer

DECLARE @count INT

SELECT

Cat.[Name],

Rep.[ScheduleId],

Own.UserName,

ISNULL(REPLACE(Sub.[Description],'send e-mail to ',''),' ') AS Recipients,

Sub.[LastStatus],

Cat.[Path],

Sub.[LastRunTime]

INTO

#tFailedSubs

FROM

dbo.[Subscriptions] Sub with (NOLOCK)

INNER JOIN

dbo.[Catalog] Cat with (NOLOCK) on Sub.[Report_OID] = Cat.[ItemID]

INNER JOIN

dbo.[ReportSchedule] Rep with (NOLOCK) ON (cat.[ItemID] = Rep.[ReportID] and Sub.[SubscriptionID] =Rep.[SubscriptionID])

INNER JOIN

dbo.[Users] Own with (NOLOCK) on Sub.[OwnerID] = Own.[UserID]