1 Apr 2025
How to setup SSRS Emails on Failure
The Problem I wanted to solve
- I needed a way to get notified when SSRS reports would fail for any reason
- The best way that I thought would be emails on failure.
ExecutionLogStorage Table
- Contains the log of all successful and unsuccessful report executions.
- Below is a screenshot of the output from the useful ExecutionLog2 view. This view translates many bit codes used in SSRS into human-readable values. For example, a column RequestType of 1 is translated to “Subscription.”
Structure of code
- An After Insert Trigger was created to be invoked on every insert into the ExecutionLogStorage Table
- A cursor is created to go over all of the records in the result set
- The table Inserted is autogenerated from the trigger containing a table of all of the records that have been inserted
- The result set skeleton is comprised from the following query which joins with the Catalog table to get the report that it is associated with:
SELECT
i.col1
i.col2
...
c.col1
c.col2
...
FROM
Inserted i
Left Join Catalog c WITH (NOLOCK) on c.ItemID = i.ReportID
- The skeleton for the while loop going through each of the records in the result set and emailing if there was an error is below
While @@FETCH_STATUS = 0
Begin
IF ( Coalesce(@e_Status, 'null') <> 'rsSuccess' and @e_ItemAction = 'Render' )
Begin
set @body_string = Concat(
-- construct
)
set @subject_string = Concat(
-- construct
)
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='<Your profile name here>',
@recipients ='johndoe@hotmail.com',
@subject = @subject_string,
@body = @body_string;
End;
fetch next from mycursor into @var1, @var2
End;
Full Code
CREATE TRIGGER [dbo].[sendFailedEmail]
ON [dbo].[ExecutionLogStorage]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- ExecutionLogStorage columns
DECLARE @e_LogEntryId INT;
DECLARE @e_UserName Nvarchar(255);
DECLARE @e_Format Nvarchar(255);
DECLARE @e_ReportAction VarChar(255);
DECLARE @e_TimeStart DateTime;
DECLARE @e_TimeEnd DateTime;
DECLARE @e_Source Varchar(255);
DECLARE @e_Status NVarChar(255);
DECLARE @e_RowCount BigInt;
DECLARE @e_RequestType Varchar(128);
DECLARE @e_ItemAction Varchar(128);
-- Catalog columns
DECLARE @c_ReportPath Varchar(max);
DECLARE @c_Name NVarChar(255);
-- where the body will be stored
DECLARE @body_string NVarChar(Max);
-- where the subject will be stored
DECLARE @subject_string NVarChar(Max);
-- declare & init cursor
Declare mycursor cursor for
(
SELECT
i.LogEntryId,
i.UserName,
i.Format,
CASE WHEN i.ReportAction = 1 THEN 'Render'
WHEN i.ReportAction = 2 THEN 'BookmarkNavigation'
WHEN i.ReportAction = 3 THEN 'DocumentMapNavigation'
WHEN i.ReportAction = 4 THEN 'DrillThrough'
WHEN i.ReportAction = 5 THEN 'FindString'
WHEN i.ReportAction = 6 THEN 'GetDocumentMap'
WHEN i.ReportAction = 7 THEN 'Toggle'
WHEN i.ReportAction = 8 THEN 'Sort'
ELSE 'Unknown'
END AS ReportAction,
i.TimeStart,
i.TimeEnd,
CASE
WHEN Source = 1 THEN 'Live'
WHEN Source = 2 THEN 'Cache'
WHEN Source = 3 THEN 'Snapshot'
WHEN Source = 4 THEN 'History'
WHEN Source = 5 THEN 'AdHoc'
WHEN Source = 6 THEN 'Session'
WHEN Source = 7 THEN 'Rdce'
ELSE 'Unknown'
END AS Source,
i.[Status],
i.[RowCount],
COALESCE(c.Path, 'Unknown') AS ReportPath,
CASE
WHEN RequestType = 0 THEN 'Interactive'
WHEN RequestType = 1 THEN 'Subscription'
ELSE 'Unknown'
END AS RequestType,
c.name AS Name,
CASE
WHEN ReportAction = 1 THEN 'Render'
WHEN ReportAction = 2 THEN 'BookmarkNavigation'
WHEN ReportAction = 3 THEN 'DocumentMapNavigation'
WHEN ReportAction = 4 THEN 'DrillThrough'
WHEN ReportAction = 5 THEN 'FindString'
WHEN ReportAction = 6 THEN 'GetDocumentMap'
WHEN ReportAction = 7 THEN 'Toggle'
WHEN ReportAction = 8 THEN 'Sort'
WHEN ReportAction = 9 THEN 'Execute'
WHEN ReportAction = 10 THEN 'RenderEdit'
WHEN ReportAction = 11 THEN 'ExecuteDataShapeQuery'
WHEN ReportAction = 12 THEN 'RenderMobileReport'
WHEN ReportAction = 13 THEN 'ConceptualSchema'
WHEN ReportAction = 14 THEN 'QueryData'
WHEN ReportAction = 15 THEN 'ASModelStream'
WHEN ReportAction = 16 THEN 'RenderExcelWorkbook'
WHEN ReportAction = 17 THEN 'GetExcelWorkbookInfo'
WHEN ReportAction = 18 THEN 'SaveToCatalog'
WHEN ReportAction = 19 THEN 'DataRefresh'
ELSE 'Unknown'
END AS ItemAction
from
Inserted i
Left Join Catalog c WITH (NOLOCK) on c.ItemID = i.ReportID
);
-- open the cursor
open mycursor;
-- fetch the result
fetch next from mycursor into @e_LogEntryId, @e_UserName, @e_Format, @e_ReportAction, @e_TimeStart,
@e_TimeEnd, @e_Source, @e_Status, @e_RowCount, @c_ReportPath, @e_RequestType, @c_Name, @e_ItemAction;
-- loop through the results
While @@FETCH_STATUS = 0
Begin
-- send out error email if it is not successfull or if status is null
IF (Coalesce(@e_Status, 'null') <> 'rsSuccess' and @e_ItemAction = 'Render')
Begin
--construct the body
set @body_string = Concat(
'Report:: ', @c_Name, Char(13),Char(10),
'Status:: ',@e_status, Char(13),Char(10),
'ReportPath:: ', @c_ReportPath, Char(13),Char(10),
'TimeStart:: ', @e_TimeStart, ' => TimeEnd: ', @e_TimeEnd, Char(13),Char(10),
'Username:: ', @e_UserName, Char(13),Char(10),
'Source:: ', @e_Source, Char(13),Char(10),
'Request Type:: ', @e_RequestType, Char(13),Char(10),
'Report Action:: ', @e_ReportAction, Char(13),Char(10),
'Report Format:: ', @e_Format, Char(13),Char(10),
'Row Count:: ', @e_RowCount, Char(13),Char(10)
)
-- construct the subject
set @subject_string = Concat(
'SSRS ERROR:: [', @e_RequestType, '] report [', @c_Name, '] has FAILED with status of ', @e_Status
)
---- send the email
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='<Your profile name here>',
@recipients ='johndoe@hotmail.com',
@subject = @subject_string,
@body = @body_string;
End;
--Else
-- Begin
-- do something if it was successful
-- End;
-- get the next record
fetch next from mycursor into @e_LogEntryId, @e_UserName, @e_Format, @e_ReportAction, @e_TimeStart,
@e_TimeEnd, @e_Source, @e_Status, @e_RowCount, @c_ReportPath, @e_RequestType, @c_Name, @e_ItemAction;
End;
-- close the cursor
close mycursor;
-- deallocate resources for the cursor
deallocate mycursor;
END;
