Getting error reports when a SQL Reporting Subscription Fails

UPDATE 2:

Either this never worked in the first place as I wanted, or MS has changed how they are doing things in SSRS 2008.

I now don’t see any of the errors in the tables I had been looking in, and in fact, the errors don’t show up in any of their logs either.  This is crap.

If you are like me, you don’t like the fact that the first time you realize that a data driven subscriptions in SQL Server Reporting Services has been failing is when someone comes up to you and says “Hey, I did XYZ and I never saw the server kick off an email.” 

Really?  Let me check…. oh, looks like it has been broken for god knows how long.  What the heck?

Well, I wrote a script to notify you when there are errors on the report server.

First, you need to setup Database Mail.  Expand Management, and pick Database Mail.  For my script I used the name “Email Profile” for the “Email Profile” get it?

Then create a trigger on the ExecutionLogStorage table like this:

USE [ReportServer]
GO
/****** Object:  Trigger [dbo].[ExecutionLogStorage_Insert]    Script Date: 05/13/2009 10:20:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ExecutionLogStorage_Insert]
ON [dbo].[ExecutionLogStorage]
AFTER INSERT
AS
BEGIN
    DECLARE @sStatus AS NVARCHAR(32)
    SET @sStatus = (SELECT Status FROM inserted)
    
    IF(@sStatus <> 'rsSuccess')
    BEGIN
        DECLARE @sReportName AS NVARCHAR(425)
        DECLARE @sPath AS NVARCHAR(425)
        DECLARE @ReportId AS UNIQUEIDENTIFIER
        DECLARE @sUserName AS NVARCHAR(260)
        DECLARE @sParameters AS NVARCHAR(4000)
        DECLARE @dTimeEnd AS DATETIME
        DECLARE @iTimeProcessing as int
        
        
        SELECT
          @sReportName = Name,
          @sPath =PATH,
          @ReportId = ReportId,
          @sUserName = username,
          @sParameters = (select CAST(ISNULL(ELS.Parameters,'') AS NVARCHAR(4000)) from ExecutionLogStorage ELS Where LogEntryId = Inserted.LogEntryId),
          @dTimeEnd = TimeEnd,
          @iTimeProcessing = ISNULL(TimeProcessing,0)
        FROM inserted INNER JOIN [Catalog]
        ON inserted.ReportId = CATALOG.[ItemID]
        
        DECLARE @sBody VARCHAR(8000)
        SET @sBody = 'Error with SQL Report '
                    + CAST(@sReportName AS VARCHAR(100))
                    + ' at path '
                    + CAST (@sPath AS VARCHAR(250))
                    + ' with ReportId '
                    + CAST(@ReportId AS VARCHAR(100))
                    + ' and username '
                    + CAST(@sUserName AS VARCHAR(100))
                    + ' Parameters '
                    + CAST(@sParameters  AS VARCHAR(5000))
                    + ' End time '
                    + CAST(@dTimeEnd AS VARCHAR(50))
                    + ' Status '
                    + CAST(@sStatus AS VARCHAR(50))
                    + ' Time Processing '
                    + CAST(@iTimeProcessing AS VARCHAR(50));
        
        DECLARE @sSubject VARCHAR(500)
        SET @sSubject = 'Error With Report ' + @sReportName
        
        EXEC msdb.dbo.sp_send_dbmail
         @profile_name = 'Email Profile',
         @recipients = 'email@address.com',
         @body =  @sBody  ,
         @subject = @sSubject;

    END
    
END

Presto!

 

UPDATE: Added logic to get the status and parameters.

 

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s