This article was written for SQL 2014

Summary

If you have an SSRS subscription that emails out a report, but there is a requirement to only send that report if there is data, then that should be easy enough to do, shouldn’t it?

From what I’ve found so far, there is no perfect solution to stopping a report from being emailed. However I have found 2 imperfect ways to archive this.

1. Have your report produce an error if there is no data, or
2. Edit the subscriptions job to check for data.

My Preferred Solution?

When I started writing this article, my preferred option was option 2, but after seeing the issues with each, although neither is perfect, I now believe option 1 is the best option, as it is more maintainable and option 2 has more downsides (and is just a hack).

Option 1 – Report Generates an Error if no data

With this option you simply check for data inside the report and call the RAISEERROR statement if the data doesn’t exist.

IF NOT EXISTS (SELECT 1 FROM dbo.MyTable WHERE ID = @Criteria)
BEGIN
	RAISERROR('No data available for the report.', 16, 1)
END

SELECT ID, Field1, Field2
FROM dbo.MyTable
WHERE ID = @Criteria

So the query in the SSRS Query editor should look something like this…

Using a Stored Procedure – Edit 2022-06-29

Based on a comment I received I felt I should do more with Stored Procedures, after all, they are my preferred method of getting data for reports.

Probably the easiest way is to put the RAISEERROR in the Stored Procedure itself, and it will produce the same result.

However, if you don’t want to add the RaiseError, or for some reason you can’t, then maybe try adding a RETURN value to your Stored Procedure and check that when running the report. Like so…

CREATE PROCEDURE dbo.MyStoredProc
AS
BEGIN
	IF EXISTS(SELECT * FROM dbo.MyTable)
	BEGIN
		SELECT ID, Field1, Field2
		FROM dbo.MyTable
		RETURN 1
	END
	ELSE
		RETURN 0
END

So a return value of 1 means there is data, and 0 there isn’t.

The report query would look like this…

DECLARE @returnVal INT
EXECUTE @returnVal = dbo.MyStoredProc
IF (@returnVal = 0)
BEGIN
	RAISERROR('No data available for the report.', 16, 1)
END

EXECUTE dbo.MyStoredProc

It means running the procedure twice, but hopefully that doesn’t matter when the report is sent from a subscription.

Notes

The severity needs to be greater than 10 else SSRS will send the report anyway.

Using error handling for logic flow is not exactly best practise, and that is what we are doing here.

The biggest downside for me is that SSRS doesn’t seem to show the error message, it just shows a generic message, so you can’t tell if the report is working or not. This might be problematic if you have some sort of monitoring.

Option 2 – Edit the SQL Agent to not call the Event

SSRS subscriptions are run through an SQL Agent Job. When you create a subscription it creates a new Job to run the subscription event. By editing the step that fires the event we can stop the event from being called.

However, first off, we have to find the job that calls the event. Not so easy when all the jobs look like this…

Finding the right SQL Agent Job

As can be seen in the image above, the name of SQL Agent job is a GUID, and because there can be so many jobs, finding the one you are after can be difficult and time consuming.

The link between the Agent job and the subscription is the subscription ID. The script below is what I use to get the Job name.

/* -----------------------------------------------------------------
 * Use this script to find the SQL Agent for a report subscription
 * Assumes the Report Server is called ReportServer
 * ----------------------------------------------------------------- */

 SET NOCOUNT ON

DECLARE @ReportName NVARCHAR(425) = 'ODC - File Part Warning'
DECLARE @SubscriptionID NVARCHAR(100)

-- get the subscription ID
SELECT @SubscriptionID = s.SubscriptionID 
FROM  ReportServer.dbo.Catalog c
	INNER JOIN ReportServer.dbo.Subscriptions s ON s.Report_OID = c.ItemID
WHERE c.Name = @ReportName

-- Output the Subscription ID
PRINT @SubscriptionID

-- get the Agent Job Name
SELECT sj.name JobName
FROM msdb.dbo.sysjobs sj
	INNER JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE sjs.command LIKE '%' + @SubscriptionID + '%'

The output should look like this…

Editing the SQL Agent Job to not call the event

Got to the properties for the Job with the name (retrieved above).

The Job contains one step with one command that looks like this…

exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='4dbe3c08-c55a-461a-b213-02796e90c280'

Edit that step to check for data before calling the event, like this…

IF EXISTS (SELECT 1 FROM dbo.MyTable WHERE Field1 = 'Criteria')
BEGIN
	exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='4dbe3c08-c55a-461a-b213-02796e90c280'
END

Notes

The Service Account ‘NT SERVICE\SQLServerReportingServices’ needs to have access to the database object(s) being queried.

You can not use the report or subscription parameters in the job, you will have to recreate the values for the IF EXISTS.

The biggest negative for this option is that the SQL Agent Job gets recreated every time the subscription is edited, meaning you need to remember to edit the job ever time you edit the subscription.

Conclusion

For me it is a trade off, do I have a subscription that displays errors when there are none, or do I hack the job, and try to remember to do it every time the subscription is updated?

Maintainability is a major aspect of Best Practice, and for me, the transparency of Option 1 far out weighs the benefits for Option 2. Option 2 is just too much of a hack.