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 “PRODSERVER17FML27” 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

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s