SSRS Failed Subscription Notifications

Case

A nice feature of Reporting Services is that it can email reports on a regular basis to certain recipients including attachments in several file types. After having implemented several reports with a lot of subscriptions to different recipients it becomes a bit of a headache to monitor if reports are still delivered.

SSRS is quite picky. It takes only one invalid email adres to cause the report not be sent. To no one. If one of the report parameters is changed and you forget to change the subscription the whole thing fails. With no notification.

I would like to know which subscriptions on reports failed the last x number of days.

Solution

I created a job on SQL server with one step that executes every morning. That job creates a HTML table in the email body telling me which subscriptions failed over the last 48 hours.

Step 1: Create a new job

Step 2: Create a new step, give it a nice name and choose for Type: Transact-SQL script (T-SQL). Save the SQL code you can find further below on this page, save it, create a schedule and you are good to go.

Replace <SERVERNAME> with the server name where the reporting services (databasename: ReportServer) are running.

DECLARE @TableHTML NVARCHAR(MAX);
	
	SET @TableHTML = 
		N'<h1> Failed Report Subscriptions Last 48 Hours</H1>' +
		N'<table border="1">' +
		N'<tr><th>Name</th><th>LastRunTime</th><th>LastStatus</th><th>Description</th></tr>' +
		CAST ((
			SELECT	td = C.Name, '',
					td = S.LastRunTime, '',
					td = S.LastStatus, '',
					td = S.Description
			FROM	<servername>.ReportServer.dbo.Subscriptions AS S
			LEFT OUTER JOIN <servername>.ReportServer.dbo.[Catalog] AS C
					ON C.ItemID = S.Report_OID
			WHERE	LEFT (S.LastStatus, 12) != 'Mail sent to'
			AND		LEFT (S.LastStatus, 12) != 'New Subscrip'
			AND		RIGHT (S.LastStatus, 9) != '0 errors.'
			AND		S.lastruntime > dateadd(hour,-48,cast(cast(GETDATE() as DATE) as datetime)) FOR XML PATH('tr'), TYPE
		) AS NVARCHAR(MAX) ) + N'</table>' ;

    EXEC msdb.dbo.sp_send_dbmail  
    @recipients = 'your.address@email.com',
--    @copy_recipients = <cc field>,
    @profile_name = 'System Administrator',
    @subject = 'SSRS Subscription Failures',
    @body = @TableHTML,
    @body_format = 'HTML';