Managing Subscription Failures in SSRS

SQL Server Reporting Services (SSRS) is a mature product from Microsoft that has been implemented in every environment that I have ever worked in with SQL Server in place. Microsoft’s newer “Power” offerings are sure to replace SSRS in the future, but adoption rates for the “Power” offerings and the current flexible rendering and delivery features available in SSRS will keep this tool running on corporate servers for the next few years at least (Case in point, see Building Real-World Microsoft BI Dashboards Today by Jen Underwood).

SSRS’ flexibility in features also translates to how it can be implemented and managed. The expectations from Microsoft are that Power Users and Developers can create report definitions, while end users are capable of managing scheduling and delivery (with the exception of data driven subscriptions, which allows power users and IT to create subscriptions en masse). I have seen implementations across this spectrum, including where SSRS is tightly controlled by IT, and all report definitions and subscriptions are created by developers and administrators only (which equals a lot of support tickets).

Regardless of how SSRS is implemented in your organization, it ultimately falls to IT to address support issues in scheduling and delivery. What Microsoft doesn’t give you with SSRS are any centralized tools for monitoring an entire instance out of the box. Fortunately, you can use SSRS to create reports that monitor itself (with the exception of the service itself being down, which hopefully you have other mechanisms for monitoring in a production environment).

Any implementation of SSRS creates a ReportServer database in which a fairly user friendly schema resides. With some minimal poking around, I was able to construct a query to return a list of all subscriptions that failed during the last execution:

--Use variables to build link to report manager subscription management page
DECLARE @VirtualDirectory nvarchar(50) = 'Reports_JAREDZSANDBOX', @ServerName nvarchar(50) = 'jaredz-pc3';

SELECT Catalog.Name AS ReportName
,'http://' + @ServerName + '/' + @VirtualDirectory + '/Pages/Report.aspx?ItemPath=' + Catalog.Path + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab' AS ReportSubscriptionMgrUrl
,Users.UserName AS SubscriptionOwner
,Subscriptions.Description AS SubscriptionDescription
,Subscriptions.LastStatus
,Subscriptions.LastRunTime
FROM [dbo].[ReportSchedule]
INNER JOIN [dbo].[Schedule]
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN [dbo].[Catalog]
ON ReportSchedule.ReportID = Catalog.ItemID
INNER JOIN [dbo].[Subscriptions]
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [dbo].[Users]
ON Subscriptions.OwnerID = Users.UserID
WHERE (Subscriptions.DataSettings IS NULL AND Subscriptions.LastStatus LIKE 'Failure%') -- handle standard subscription errors
OR (Subscriptions.DataSettings IS NOT NULL AND RIGHT(Subscriptions.LastStatus, 11) <> '; 0 errors.') --handle data driven subscription errors
; - See more at: http://magenic.com/BlogArchive/ManagingSubscriptionFailuresinSSRS#sthash.CJGNTP0F.dpuf

I don’t claim my conditions handle the universe of enumerations, but you can groom them to your environment if you know of others that I may be missing.

Now that we have a reasonable query defined, let’s build a report for our ReportServer data and have it alert us when new failures are logged. Note: for simplicity, I remove the variables from the query and replace with full text string when adding to SSRS.

I created the Report Name column as a hyperlink to the ReportSubscriptionMgrUrl field value, which will bring you directly to the subscription management page for that report:

Finally, why wait to visit the report before you know something is wrong? Have it pushed to you automatically by creating a data alert (SSRS integrated mode), or subscribe on a schedule if using the stand-alone version.