How to prevent firing of subscriptions when there is no data in Report?

| 2 Comments

Many times it is annoying to receive email alert when there is no data in the report. Generally such cases happen when you have scheduled an email subscription for a report which fires at the specified schedule and delivers the report. However as an end user you might be interested to receive an email alert only when there is data in the report.

To deal with such situation Reporting Services doesn’t have an Out of Box solution, so in this Blog I would like to discuss on how I managed to achieve to this

Method 1: (Preferred Method for Enterprise Edition of SQL Server)

Use Data Driven Subscriptions

If you are using Data Driven Subscriptions, you can prevent the Subscriptions being fired by modifying the query used for Data Driven Subscriptions as

select * from <<Data driven subscription table>>
where exists ( select top 1  << query which populates the dataset in report and select only one column in the query>>)

Make sure to use top 1 in query to fetch only 1 row and select only 1 column in the column list in the query since the intention is only to check the existence of data from the query

Method 2: (Preferred Method for Other Editions Of SQL Server)

Use Custom T-SQL Script to trigger the subscription Job

The reason I list this method as preferred method for other editions is since Data driven subscriptions is available only with Enterprise Edition of SQL Server and hence for all editions we will have to use this method.

As we know, each subscription creates a Job in SQL Agent which has named on GUID generated internally. At the defined schedule, the sql agent job kicks in to run the report and sends the report to the defined subscribers.

We use the same concept, however in our case we first need to check the existence of the data in the report and if data exist we can invoke the job.

We cannot afford to modify the existing job since it will land us in unsupported scenario.

So we first modify the schedule of subscriptions to run “once” on a schedule date of the past (may be yesterday). You can do this using Report Manager where you go the subscriptions tab for the report and edit the schedule. We do this so that report subscription remains active and enabled but at the same time doesn’t invoke the Job since we will invoke the job programmatically using our scripts.

We now create a new T-SQL job and add the following script. We schedule this job to run on the same schedule as the original subscription was initially configured

IF EXISTS
(
<<SELECT TOP 1 query to check data existence>>
)
BEGIN
DECLARE @Jobid uniqueidentifier
DECLARE @Subscriptions TABLE
(Jobid uniqueidentifier)

INSERT INTO @Subscriptions
select sch.ScheduleID from Schedule sch
INNER JOIN Subscriptions subs ON sch.[EventData]=subs.SubscriptionID
INNER JOIN Catalog cat ON subs.Report_OID=cat.ItemID
where cat.Name=<<Report Name>>

select TOP 1 @Jobid =Jobid from @Subscriptions
WHILE(@@ROWCOUNT<>0)
BEGIN
EXEC msdb.dbo.sp_start_job @Jobid
DELETE FROM @Subscriptions where Jobid=@Jobid
select TOP 1 @Jobid =Jobid from @Subscriptions
END
END

So the new job created will run on the defined schedule, it will check for the existence of the data in the report and if data exists, it will invoke the subscriptions job which will run the report and send the report to subscribers.

Hope this helps !!!!

Parikshit Savjani
Premier Field Engineer

2 Comments

  1. Hi Parikshit

    Great article and very useful. However, I have never created T-SQL jobs and don’t know where should I create the script you shared. Can you give some pointers on where to go to make this work?

    Thank you

    Mário

  2. Hi Parikshit, using Method 2, I’d like to also handle the issue of no data is available currently because a process isn’t complete (yet) but may/will be at a later time, could the job be set to test multiple times until there is data available?

Leave a Reply

Required fields are marked *.