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.
After deploying the report I can see it on the intranet site.
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.
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.
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.
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.
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'
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:
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.