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.
Chris May 