T-SQL Tuesday #25–Did Your RS Report Fail?
Dec 13, 2011 at 8:57 AM Allen White (blog|twitter), in hosting this months T-SQL Tuesday, is looking for tricks to help make you more effective.
I could probably try and figure out lots of things that I mess around with to make myself more effective. For example adding aliases for my servers so that instead of having to go to “PRODSERVER17\FML27” I can just go to “FML27”.
Instead of that I thought I’d throw something up real quick that turned up as an issue the other day at work.
We have a bunch of scheduled Reporting Services reports that get emailed to users and files put out on shares. These schedules are actually run as SQL Server Agent jobs. We ran into an issue whereby a report failed over a weekend. We knew nothing about the failure, the first we knew was when the customer started complaining that the report was not available.
Knowing that the schedules are running in SQL Agent I looked for failed jobs. There were none. All the job does is submit the required report into the job queue and then say that it did it’s job. It does not actually monitor successful execution of the report itself. There doesn’t actually appear to be any quick and simple way of doing this. As such I wrote a process that goes out to the Reporting Services database on a daily basis, looks for reports that have failed and then sends me a report to let me know. It’s a good way to get ahead of the customer and find problems before they are reported.
Code below (in this code the default database ReportServer is used for the Reporting Services database)
/*
Query grabs a list of the failed Reporting Services reports for the last 24 hours.
This list is then sent as an HTML table via email.
Data in email includes:
Report Name
Path To Report
Username Executing Report
Date/Time When Report Executed
Parameters Used In Report Execution
*/
SET NOCOUNT ON
DECLARE @HTML VARCHAR(MAX)
DECLARE @HTML_Header VARCHAR(MAX)
DECLARE @HTML_Body VARCHAR(MAX)
DECLARE @HTML_Footer VARCHAR(MAX)
DECLARE @COUNT INT
--create the table to hold the dataset
DECLARE @Results TABLE
(
ID INT IDENTITY(1, 1) ,
RSName NVARCHAR(850) ,
RSPath NVARCHAR(850) ,
UserName NVARCHAR(520) ,
TimeStart DATETIME ,
Parameters NVARCHAR(MAX)
)
--Grab the data from the ReportServer database for failed reports
INSERT INTO @Results
( RSName ,
RSPath ,
UserName ,
TimeStart ,
Parameters
)
SELECT c.Name ,
c.Path ,
[UserName] ,
[TimeStart] ,
[Parameters]
FROM [ReportServer].[dbo].[ExecutionLogStorage] els
INNER JOIN ReportServer.dbo.Catalog c ON els.ReportID = c.ItemID
WHERE status != 'rsSuccess'
AND TimeStart > DATEADD(hh, -24, GETDATE())
ORDER BY LogEntryId
--create the email
SELECT @count = MAX(ID)
FROM @Results
IF @count <> 0
BEGIN
SET @HTML_Header = '<html><header></header><body>
<TABLE>
<table border="1" cellspacing="1" cellpadding="1">
<TR colspan=2style="color: #A0522D; font-family: Verdana; font-size: 11; font-weight:bold" align=Left>
<td>Report Name</TD>
<TD>Report Path</TD>
<TD>Executed On</TD>
<TD>Executed By</TD>
<TD>Parameters</TD>
</TR>
'
SET @HTML_FOoter = '</TABLE>
<BR><BR>
<FONT FACE="color: #A0522D; font-family: Verdana; font-size: 12";></FONT></body></html>
'
END
ELSE
BEGIN
SET @HTML_Header = 'No failed reports in the last 24 hours'
SET @HTML_Footer = '</TABLE>'
END
SELECT @HTML_Body = ''
WHILE @count > 0
BEGIN
SELECT @HTML_Body = @html_body + '<TR style="font-size: 10">
<TD style="text-align: left;" >' + RSName + '</TD>
<TD style="text-align: LEFT;">  ' + RSPath
+ '</TD>
<TD>  ' + CONVERT(VARCHAR, timestart, 120)
+ '</TD>
<TD style="text-align: left;">  ' + Username
+ '</TD>
<TD style="text-align: left;">  '
+ ISNULL(Parameters, '') + '</TD>
</TR>'
FROM @results
WHERE ID = @count
SET @Count = @count - 1
END
SET @HTML = @HTML_Header + @HTML_Body + @HTML_FOoter
EXEC MSDB.DBO.SP_SEND_DBMAIL @RECIPIENTS = '<Your Email Address>',
@SUBJECT = 'Failed RS Reports - Last 24 hours', @BODY = @HTML,
@BODY_FORMAT = 'HTML'
--, @IMPORTANCE = 'HIGH'
GO
SSRS, TSQL2SDAY in SSRS, T-SQL Tuesday 
