T-SQL Tuesday #25–Did Your RS Report Fail?

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;">&nbsp ' + RSPath
                + '</TD>
                           <TD>&nbsp ' + CONVERT(VARCHAR, timestart, 120)
                + '</TD>
                           <TD style="text-align: left;">&nbsp ' + Username
                + '</TD>
                           <TD style="text-align: left;">&nbsp '
                + 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