Stop Empty Reports from being Emailed in Reporting Services

My grandpa owns a cabin on a medium sized lake up in Oneida County. This lake is very clear (often described as gin-clear), and so fishing it presents certain challenges. Since the water is clear, you need to make sure your presentation looks natural. Lures with bright colors like fire-tiger are usually not the way to go. You also often have to fish deeper, since the sunlight penetrates further and weeds can grow in deeper depths. Since I grew up fishing this lake, I got used to clear lake fishing. I feel like I’m a better clear-lake fisherman. Maybe that’s why I prefer fishing Okauchee over Pewaukee here in southeast Wisconsin. Pewaukee and Okauchee are the only two lakes in SE WI that are rated A class muskie lakes by the Wisconsin DNR (Department of Natural Resources). Pewaukee gets all the publicity, but Okauchee is a great lake too. Pewaukee has fairly cloudy water, and is a shallower lake while Okauchee is a clear, deep lake. I’ve had much better success on Okauchee, and that’s why it’ll be the first lake I get on when fishing opens on May 3rd.
SQL Server Reporting Services SSRS has the ability to send reports out on a regular basis. This is a very helpful tool because we can put the data right in the users’ inboxes, making it easy for them to get and use the data. The scheduling functionality in SSRS is pretty flexible, but one important piece thats missing is the ability to not send empty reports. If the SQL statements used to send the report produce zero rows, an empty report gets emailed out to the recipients. I decided to look for a way to prevent this from happening. To demonstrate, let’s create a bit of database structure. First I created a table with a few records:

CREATE TABLE dbo.FavoriteLakes
    (
      id INT IDENTITY(1, 1) ,
      LakeName VARCHAR(50)
    )
INSERT  INTO dbo.FavoriteLakes
        ( LakeName )
VALUES  ( 'Two Sisters' ),
        ( 'Little Arbor Vitae' ),
        ( 'Okauchee' ),
        ( 'Pewaukee' ),
        ( 'Little Green Lake' ),
        ( 'Lake Tomahawk' )

Next I created a stored procedure that will return the results of this table. The stored procedure will be the data source for our SSRS report.

CREATE PROCEDURE [dbo].DisplayFavoriteLakes

AS
BEGIN
   SELECT LakeName
   FROM dbo.FavoriteLakes
END

Next I created a very simple SSRS report using BIDS. The report used an embedded data set with the stored procedure as the source, and had a textbox title and a table with a single row for the data.
20150318 SSRS Scheduler Mod
After deploying the report I can see it on the intranet site.
20150318 SSRS Scheduler Mod intranet
Next I set up a subscription for this job, but I stop before actually pressing OK. It will be sending me an email every five minutes.
20150318 SSRS Scheduler Mod Schedule Before actually sending the job I needed to take a screenshot of the top of my job scheduler in SQL Server Agent. Those jobs where the name looks like a GUID are the jobs associated with scheduled subscriptions in SSRS.
20150318 SSRS Scheduler Before Job Scheduled
Next I hit OK in Reporting Services to create the subscription. Then I refreshed the SQL Server Agent jobs and took another look. Now I see one extra job, so I know this is the one that is associated with the subscription I just created.

20150318 SSRS Scheduler AFter Job Scheduled

Upon going into the job the first thing I see is a warning not to modify the job. Doing so can result in database incompatabilities. I had to change the owner from the service account that runs SQL Agent to sa. It is important to note that any changes to the job or to delete the job would require me to change the owner back.
20150318 SSRS Scheduler Job Summary
When I go into the single job step for this job, I see a single line of SQL:

 [LW_ReportDB].dbo.AddEvent @EventType = 'TimedSubscription',
    @EventData = 'e20a1691-792b-4f8f-938e-ac4b5ed25b69'

20150318 SSRS Scheduler Mod Job Step
I’m going to change that SQL to the following:

SELECT TOP 1 1
FROM DBMaint.dbo.FavoriteLakes

IF @@ROWCOUNT > 0
BEGIN
   EXEC [LW_ReportDB].dbo.AddEvent @EventType = 'TimedSubscription',
       @EventData = 'e20a1691-792b-4f8f-938e-ac4b5ed25b69'
END

This takes the existing code and only call it if there is at least one record in the dbo.FavoriteLakes table.
Before and after this change I am receiving the following email:
20150318 SSRS Scheduler Mod Email
However, after clearing out the table:

TRUNCATE TABLE DBMaint.dbo.FavoriteLakes

I am no longer receiving the email. I’ve now made the subscription dependant upon having a result set.