Sending Alerts when Reporting Services job fail

There’s a lot of gear to remember when you head out for a fishing trip. You need your rods/reels, lures, live bait, net, fishing license, sunscreen, bug spray, hat, water, snacks, beer, and one of the most important things to remember – put the plug in the boat before you launch! A recent fishing trip to Lake Winnebago got off to a rocky start. First, I did forget to put the plug in. Once I noticed water pouring in I quickly screwed in the plug and got the bilge pump running. Next, after we motored out about a half mile to an offshore reef I looked in the cooler only to discover that I’d left the worms at home. We had to motor all the way back and swing by a nearby gas station to pick up some more.
It can be frustrating when we are expecting something to be there, but it’s not. We have several SQL Server Reporting Services (SSRS) reports that are scheduled to run at certain times throughout the week. These reports are sent to internal users for making everyday business decisions. Unfortunately, if the reports fail for any reason, they are simply not sent. I wanted to make sure everyone was getting their reports, so I set up a SQL Agent job to alert me for job failures. I was able to take advantage of the ReportDB.dbo.Subscriptions table to looks for statuses that weren’t “Mail sent to” or “New Subscrip”.

SELECT  c.Name ,
        S.LastRunTime ,
        S.LastStatus ,
        S.Description ,
        c.Path
FROM    LW_ReportDB.dbo.Subscriptions S
        LEFT OUTER JOIN LW_ReportDB.dbo.Catalog c ON c.ItemID = S.Report_OID
WHERE   LEFT(S.LastStatus, 12) != 'Mail sent to'
        AND LEFT(S.LastStatus, 12) != 'New Subscrip' 

This query will show any scheduled jobs that didn’t run as expected. Using my stored procedure that I created to send emails in html table format, I run the query like this:

EXEC DBMaint.dbo.SendDBEmailwithTabularQuery 
   @qSELECT = N'SELECT', 
    @fieldlist = N'C.Name|S.LastRunTime|S.LastStatus|S.Description|C.Path', 
    @qFROM = N'FROM LW_ReportDB.dbo.Subscriptions AS S
				LEFT OUTER JOIN LW_ReportDB.dbo.[Catalog] AS C
				ON C.ItemID = S.Report_OID',
    @qWHERE = N'WHERE LEFT (S.LastStatus, 12) != ''Mail sent to''
				AND LEFT (S.LastStatus, 12) != ''New Subscrip''',
    @recipients = N'dgugg@domain.com', 
    @subject = N'SSRS Reports that have failed'

And I get results like this:
20150126 SSRS Report Fail Notification
The last step is just to set up the query in a SQL Agent job to run each morning. Now when I get in each day I can investigate any failures before the report user has a chance to even notice it failed.

Advertisements

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s