SSRS Failed Subscription Notifications

Reporting Services can email reports to end users on a regular basis without allowing them access to the web portal. This gives you a little more control over the report and can make it easier for the end user. One problem is that with numerous reports scheduled with numerous parameters to numerous recipients it can be difficult to know if/when a report failed to be sent. It looks bad when the VP keeps asking why he didn’t get his report today.

SSRS subscriptions are very picky. If you have one invalid email address in a subscription with multiple recipients, the whole subscription fails and nobody gets the report. Likewise, if one of your report parameters changed the report will not be sent. Surprisingly there isn’t an out of the box solution to see what reports failed to run.

Here is a query I wrote that checks SSRS subscriptions and returns any failed reports. Use the database you setup for Reporting Services. (default is ReportServer)

SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description
FROM Subscriptions AS S
ON C.ItemID = S.Report_OID
WHERE LEFT (S.LastStatus, 12) != ‘Mail sent to’
AND LEFT (S.LastStatus, 12) != ‘New Subscrip’

The first time I ran this we had 4 reports that had failed and we weren’t aware of it! I’ve set this up as an SSRS report and view it daily, and have it emailed to me so I know all my reports were sent.


10 responses to “SSRS Failed Subscription Notifications

  1. I know this takes it to another level of complication, but couldn’t you set it to email to one email box that has rules set up to forward the report onto your desired list of recipients? This would bypass the “lack of email features” as it would have less to be picky on.

  2. interesting idea. I suppose you could have it sent to a distribution group and if you had one invalid address it would most likely still send it to the other valid addresses. I’m not real familiar with maintaining email groups and not sure how you would find out if a report wasn’t sent to someone.

  3. I have users, well managers who want to receive notifications saying their subscriptions have failed or have the failed notifications sent to an administrator because they do not want to go to report manager page and have to click on My subscriptions to view if there is a problem.

    Is there anyway of setting that up?
    The script you have provided(thanks btw) is very useful to detect the problamatic ones.

  4. Ameet,
    You can do a couple of things. I setup the query in reporting services and have it emailed out a couple times each morning. You then can check it from your email and see if any reports failed. A more ellaborate method, which I also did, could be to setup a query that runs say every minute and looks for failed reports and puts that info into another table.
    Here is the script to create the table:
    CREATE TABLE [dbo].[SubscriptionFailureHistory](
    [failureID] [int] IDENTITY(1,1) NOT NULL,
    [catName] [varchar](425) NULL,
    [subLastRunTime] [datetime] NULL,
    [subLastStatus] [varchar](260) NULL,
    [subDescription] [varchar](512) NULL,
    CONSTRAINT [PK_SubscriptionFailureHistory] PRIMARY KEY CLUSTERED
    [failureID] ASC
    ) ON [PRIMARY]

    The job could look something like this:

    — check for report subscription failures
    DECLARE @recordCount INT;
    SET @recordCount = (SELECT COUNT(*) FROM dbo.Subscriptions AS S LEFT OUTER JOIN 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’))

    IF @recordcount > 0
    INSERT INTO dbo.SubscriptionFailureHistory
    (catName, subLastRunTime, subLastStatus, subDescription)

    SELECT C.Name AS catName
    , S.LastRunTime AS subLastRunTime
    , S.LastStatus AS subLastStatus
    , S.Description AS subDescription
    FROM dbo.Subscriptions AS S LEFT OUTER JOIN 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 on that table, setup a trigger which sends an email when an entry is added. My trigger looks something like this:

    USE [ReportServer]
    /****** Object: Trigger [dbo].[trReportFailureNotification] Script Date: 11/13/2008 08:10:11 ******/
    — =============================================
    — Author: Jeff Prom
    — Create date: 8/19/2008
    — Description: Trigger to notify on report failures
    CREATE TRIGGER [dbo].[trReportFailureNotification]
    ON [dbo].[SubscriptionFailureHistory]
    — SET NOCOUNT ON added to prevent extra result sets from
    — interfering with SELECT statements.

    DECLARE @subjectStr VARCHAR(200)
    DECLARE @bodyStr VARCHAR(1000)
    DECLARE @catName VARCHAR(425)
    DECLARE @subLastRunTime DATETIME
    DECLARE @subLastStatus VARCHAR(260)
    DECLARE @subDescription VARCHAR(512)

    SET @catName = (SELECT catName FROM inserted)
    SET @subLastRunTime = (SELECT subLastRunTime FROM inserted)
    SET @subLastStatus = (SELECT subLastStatus FROM inserted)
    SET @subDescription = (SELECT subDescription FROM inserted)

    SET @bodyStr = ‘Name: ‘ + @catName + CHAR(13);
    SET @bodyStr = @bodyStr + ‘Last Ran: ‘ + CAST(@subLastRunTime AS VARCHAR) + CHAR(13);
    SET @bodyStr = @bodyStr + ‘Status: ‘ + @subLastStatus + CHAR(13);
    SET @bodyStr = @bodyStr + ‘Description: ‘ + @subDescription + CHAR(13);

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘DWH Email Alert’,
    — @recipients = ‘’,
    @recipients = ‘’,
    @subject = ‘Report Subscription Failed’,
    @body = @bodystr;


  5. This is the first time I’ve seen anyone confirm what I found about how an invalid email address in a RS2005
    subscription with multiple recipients causes the whole subscription to fail. Has this been fixed in a service pack? Thanks for good information.

  6. Pingback: Gloria Estefan

  7. I’m using the query you defined to identify subscription failures. We have some reports using stored proc datasources that RAISERROR purposesly if there are now rows (so the user doesn’t get an empty report). I’d like to exclude those from the query for failed subscriptions. Is there a way to get the stored proc RAISERROR value in the query so I can filter those out?

    • Thomas, did you ever get a “good” solution for the forced RAISERROR method to avoid subs being sent when they contain no data, and yet still track failed subscriptions that weren’t forced failures?

      I have a hacky solution– force the creators of the subscription to put an agreed upon keyword within the description of the subscription (“FailureOnNoRows”), and then bypass it with the other Status values

      [LastStatus] NOT LIKE ‘Mail sent to%’
      AND [LastStatus] NOT LIKE ‘The file “%’
      AND [LastStatus] NOT LIKE ‘Done%0 errors.’
      AND [LastStatus] not in (‘New Subscription’,’Pending’,’Ready’)
      AND [Description] not like ‘%FailureOnNoRows%’

      This bypasses the “forced” failures within the Subscription Failure report. Unfortunately, this ALWAYS bypasses this subscription. So if the sub fails for a different reasons – bad email address, no longer valid parameter, etc. – I won’t see it.
      Ideally finding some way to capture the RAISERROR value and tie it to the specific Execution of the Report would be ideal, but I haven’t figured out any elegant solutions yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s