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
LEFT OUTER JOIN [Catalog] AS C
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.
Jeff Prom's Data, Database, and SQL Blog 
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.
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.
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.
Thanks
Ameet
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
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) 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
BEGIN
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’)
END
And on that table, setup a trigger which sends an email when an entry is added. My trigger looks something like this:
USE [ReportServer]
GO
/****** Object: Trigger [dbo].[trReportFailureNotification] Script Date: 11/13/2008 08:10:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Jeff Prom
— Create date: 8/19/2008
— Description: Trigger to notify on report failures
=============================================
CREATE TRIGGER [dbo].[trReportFailureNotification]
ON [dbo].[SubscriptionFailureHistory]
AFTER INSERT
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
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 = ‘admin@yourcompany.com’,
@recipients = ‘admin@yourcompany.com’,
@subject = ‘Report Subscription Failed’,
@body = @bodystr;
END
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.
Pingback: Gloria Estefan
good info
I don’t normally reply to these blogs but this excited me! THANKS 🙂
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.