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 !!!!
Premier Field Engineer