I recently had to resolve this issue. I was running SQL Server Standard Edition and needed to have an SSRS subscription that behaved like a data-driven subscription. Data driven subscriptions are available in Enterprise Edition SQL Server, but not Standard.
What is a Data-Driven Subscription
This is a very useful tool in the Enterprise Edition. A regular subscription is simply a scheduled time when a report will run and can be sent to email addresses or saved to a network share. The individual subscription will only run once and utilizes the same report parameters and recipient information.
A data-driven subscription lets you run a database query that returns no results or a set of results. The report is then run as many times as there are row results in the original database query. This allows you to 2 important things:
- Not run the report if it does not have any meaningful results. e.g. Don’t send anything out.
- Run multiple reports at once with customized parameters (who it is sent to, the subject of the email, the parameters used to generate the report, etc). This allows you to scale subscriptions really easily without having to go in and create a subscription for each recipient manually every time.
But I Do Not Have SQL Server Enterprise Edition
This means you do not have the data-driven feature when creating subscriptions. If you are doing a lot of data-driven report subscriptions, it may be worth your while to pay for that license as it will be less development time and more intuitive for users to understand what is happening than what I will describe below.
So we will have to come up with an alternative method to achieve something similar.
- Publish an SSRS report if it is not published already.
- Create a regular subscription using the SSRS interface.
- Set any parameter values that are constant.
- You may set the other parameters, but they will be overwritten anyways.
- Set the schedule to have a stop date in the past, this makes sure it is active, but will not run on its own.
- Create a stored procedure to run our report.
- Code example in detail below.
- Needs to run a query that provides how many times you want the report to run and what the override parameters should be.
- Will loop over the results to:
- Override the parameters stored in the table.
- Execute the report.
- Wait until the report is finished.
- Create a SQL Agent job to run the stored procedure on a schedule.
A Note Waiting For Each Report To Finish
Calling EXEC ReportServer.dbo.AddEvent does not block the script until the report is finished. It merely adds the report to the queue to be processed. If the report takes a while to run, this script may overwrite the parameters and settings being used. So it is important to have a loop monitoring the Events table to make sure the report has completed before continuing.
Stored Procedure Example
This example will determine how many reports need to be created and run each report consecutively by waiting for the report to finish before triggering another one.
--Steps to create data-driven like subscription in SQL Server 2016 Standard Edition: --1. generate a subscription for a single user and note the subscription_id --2. create stored procedure to update settings and generate reports --3. schedule a job to run this procedure -- Notes to Remember -- Need to use a Job to run this and schedule. -- The subscription_id will be generated at the time the report subscription is created -- The subscription should be set to active with a stop date in the past so it doesn't run on its own if not desired, but is an active report subscription. IF EXISTS ( SELECT 1 FROM sysobjects WHERE id = object_id('DataDrivenSSRSSubscription_MyExampleReport) AND OBJECTPROPERTY(id, N'IsProcedure') = 1) BEGIN DROP PROCEDURE dbo.DataDrivenSSRSSubscription_MyExampleReport END GO CREATE PROCEDURE [dbo].[DataDrivenSSRSSubscription_MyExampleReport] @subscription_id varchar(50), @parameter_id int AS BEGIN DECLARE @v_email varchar(256), @v_parameter1 varchar(256) @settings varchar(4096), @params varchar(4096), @qry varchar(1024), @wait smallint DECLARE db_cursor CURSOR FORWARD_ONLY READ_ONLY FOR SELECT * FROM Some_Table WHERE Some_Table.parameter_id=@parameter_id OPEN db_cursor FETCH NEXT FROM db_cursor INTO @v_email, @v_parameter1 -- looping through the list WHILE @@FETCH_STATUS = 0 BEGIN -- wait until report has been cleared from the reporting queue SELECT @wait = COUNT(*) FROM ReportServer.dbo.Event WHERE EventData=@subscription_id WHILE @wait > 0 BEGIN WAITFOR DELAY '00:00:01' SELECT @wait = COUNT(*) FROM ReportServer.dbo.Event WHERE EventData=@subscription_id END -- update parameters before generating report SET @settings = '<ParameterValues> <ParameterValue><Name>TO</Name><Value>'+@v_email+'</Value></ParameterValue> </ParameterValues>' SET @params = '<ParameterValues> <ParameterValue><Name>Parameter1</Name><Value>'+@v_parameter1+'</Value></ParameterValue> </ParameterValues>' UPDATE ReportServer.dbo.Subscriptions SET extensionsettings = @settings, parameters = @params WHERE subscriptionid = @subscription_id IF @@error = 0 BEGIN -- run report and send email EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData=@subscription_id END FETCH NEXT FROM db_cursor INTO @v_email, @v_parameter1 END CLOSE db_cursor DEALLOCATE db_cursor END
These are some really good references that may be easier to understand. One note though, they do not cover “waiting for each report to finish before moving on to the next report.”