The company I was working for needed to move all their SSRS reports from an Enterprise Edition to a Standard Edition due to cost related reasons. We moved all the reports successfully, and they were working as expected, until a few users starting complaining that they weren't recieving some of the reports. When we checked the subscriptions on the old SSRS Report Server and compared them to the subscriptions on the new Reports Server, we saw that the Data Driven Subscriptions hadn't been restored. After some investigation, and much frustration, we realised that it was due to the fact that SQL Server Standard Edition doesn't support Data Driven Subscriptions. The company wanted the Data Driven Feature but wasn't prepared to upgrade the SQL Server to Enterprise edition, so this lead me to the below solution.
The below script should be modified to suite your environment. There are 3 parts to using this:
- Create the Split String Function
- Create the main Stored Procedure
- Build the parameters and pass them to the main stored procedure.
Split String Function
The below SplitString functions needs to be created as it is used in the script that passes the parameters through to the main stored procedure. Because the Parameters are string based separated by a comma, we need to split this up to make it usable within our code.
CREATE FUNCTION [dbo].[SplitStrings] ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), E2(N) AS (SELECT 1 FROM E1 a, E1 b), E4(N) AS (SELECT 1 FROM E2 a, E2 b), E42(N) AS (SELECT 1 FROM E4 a, E2 b), cteTally(N) AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42), cteStart(N1) AS (SELECT t.N+1 FROM cteTally t WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0)) SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000)) FROM cteStart s; GO
Main Stored Procedure
This is where the bulk of the process happens. The parameters that are passed through are used to updated the necessary ReportServer tables, enabling "Data Driven" report subscrptions. The "ExtensionSettings and Parameters" columns in the [ReportServer].[dbo].[Subscriptions] table are updated to the new values passed through, the subscription is executed, with the updated parameter and extension settings, and then the "ExtensionSettings and Parameters" columns are updated with the original Extension and Parameter settings, so that if there was a subscription in place, it will run as per originally setup.
/*
ExecuteSubscribedReport
PROCEDURE DESCRIPTION:
Creates the effect of a data driven subscription by replacing the fields in
an existing subscription with the supplied values, executing the report
and then replacing the original values.
INPUT:
@ScheduleID The Job Name in SQL Server.
@EmailTo The TO address of the email.
@EmailCC The Carbon Copy address of the email.
@EmailBCC The Blind Copy address of the email.
@EmailReplyTo The Reply TO address of the email.
@EmailBody Any text that you want in the email body. (A Side Note, the "&" sign caused me endless troubles, if using the "&" use & to create it.)
@ParameterList The parameters for the report.
OUTPUT:
None
LIMITATIONS:
ParameterValues are limited to 1000 characters
EmailBody is limited to 8000 characters
ParameterList is limited to 8000 characters total
*/CREATE PROCEDURE [dbo].[ExecuteSubscribedReport]
(@ScheduleID uniqueidentifier, @EmailTo varchar (1000) = NULL, @EmailCC varchar (1000) = NULL, @EmailBCC varchar (1000) = NULL, @EmailReplyTo varchar (1000) = NULL, @EmailBody varchar (8000) = NULL, @ParameterList varchar(max))
AS
BEGIN
SET NOCOUNT ON;
DECLARE @subscriptionID uniqueidentifier, @originalExtensionSettings varchar(8000), @originalParameters varchar(8000), @newExtensionSettings varchar(8000), @newParameters varchar(8000);
-- we need to wait for our turn at using the subscription system
WHILE EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name = '##ReportInUse')
WAITFOR DELAY '00:00:30';
CREATE TABLE ##ReportInUse (ReportID int);
-- we now need to find the subscriptionID
SELECT @subscriptionID = SubscriptionID
FROM ReportServer.dbo.ReportSchedule
WHERE ScheduleID = @ScheduleID;
-- next we save away the original values of ExtensionSettings and Parameters (we use them to make it easy put the values back later)
SELECT @originalExtensionSettings = CAST(ExtensionSettings AS varchar(8000)), @originalParameters = CAST(Parameters AS varchar(8000))
FROM ReportServer.dbo.Subscriptions
WHERE SubscriptionID = @subscriptionID;
SET @newExtensionSettings = @originalExtensionSettings;
SET @newParameters = @ParameterList;
-- if ExtensionSettings have been supplied, process them
DECLARE @ParameterValues_Tag varchar(255), @OpenTag_EmailTo varchar(255), @OpenTag_EmailCC varchar(255), @OpenTag_EmailBCC varchar(255), @OpenTag_EmailReplyTo varchar(255), @OpenTag_EmailBody varchar(255)
SET @OpenTag_EmailTo = '<ParameterValue><Name>TO</Name><Value>'
SET @OpenTag_EmailCC = '<ParameterValue><Name>CC</Name><Value>'
SET @OpenTag_EmailBCC = '<ParameterValue><Name>BCC</Name><Value>'
SET @OpenTag_EmailReplyTo = '<ParameterValue><Name>ReplyTo</Name><Value>'
SET @OpenTag_EmailBody = '<ParameterValue><Name>Comment</Name><Value>'
SET @ParameterValues_Tag = '<ParameterValues>'
SET @newExtensionSettings = STUFF(@newExtensionSettings, CHARINDEX(@OpenTag_EmailTo, @newExtensionSettings ,1) + LEN(@OpenTag_EmailTo),
CHARINDEX('</Value></ParameterValue>',@newExtensionSettings ,CHARINDEX(@OpenTag_EmailTo, @newExtensionSettings ,1) + LEN(@OpenTag_EmailTo))-(CHARINDEX(@OpenTag_EmailTo, @newExtensionSettings ,1) + LEN(@OpenTag_EmailTo)), @EmailTo)
IF @newExtensionSettings LIKE '%' + @OpenTag_EmailCC + '%'
BEGIN
SET @newExtensionSettings = STUFF(@newExtensionSettings, CHARINDEX(@OpenTag_EmailCC, @newExtensionSettings ,1) + LEN(@OpenTag_EmailCC),
CHARINDEX('</Value></ParameterValue>',@newExtensionSettings ,CHARINDEX(@OpenTag_EmailCC, @newExtensionSettings ,1) + LEN(@OpenTag_EmailCC))-(CHARINDEX(@OpenTag_EmailCC, @newExtensionSettings ,1) + LEN(@OpenTag_EmailCC)), @EmailCC)
END
ELSE IF @newExtensionSettings NOT LIKE '%' + @OpenTag_EmailCC + '%' AND COALESCE(@EmailCC,'') <> ''
BEGIN
SET @EmailCC = @OpenTag_EmailCC + @EmailCC + '</Value></ParameterValue>'
SET @newExtensionSettings = STUFF(@newExtensionSettings, CHARINDEX(@ParameterValues_Tag, @newExtensionSettings ,1) + LEN(@ParameterValues_Tag),
CHARINDEX('<ParameterValue>',@newExtensionSettings ,1)-(CHARINDEX(@ParameterValues_Tag, @newExtensionSettings ,1) + LEN(@ParameterValues_Tag)), @EmailCC)
END
IF @newExtensionSettings LIKE '%' + @OpenTag_EmailBCC + '%'
BEGIN
SET @newExtensionSettings = STUFF(@newExtensionSettings, CHARINDEX(@OpenTag_EmailBCC, @newExtensionSettings ,1) + LEN(@OpenTag_EmailBCC),
CHARINDEX('</Value></ParameterValue>',@newExtensionSettings ,CHARINDEX(@OpenTag_EmailBCC, @newExtensionSettings ,1) + LEN(@OpenTag_EmailBCC))-(CHARINDEX(@OpenTag_EmailBCC, @newExtensionSettings ,1) + LEN(@OpenTag_EmailBCC)), @EmailBCC)
END
ELSE IF @newExtensionSettings NOT LIKE '%' + @OpenTag_EmailBCC + '%' AND COALESCE(@EmailBCC,'') <> ''
BEGIN
SET @EmailBCC = @OpenTag_EmailBCC + @EmailBCC + '</Value></ParameterValue>'
SET @newExtensionSettings = STUFF(@newExtensionSettings, CHARINDEX(@ParameterValues_Tag, @newExtensionSettings ,1) + LEN(@ParameterValues_Tag),
CHARINDEX('<ParameterValue>',@newExtensionSettings ,1)-(CHARINDEX(@ParameterValues_Tag, @newExtensionSettings ,1) + LEN(@ParameterValues_Tag)), @EmailBCC)
END
IF @newExtensionSettings LIKE '%' + @OpenTag_EmailReplyTo + '%'
BEGIN
SET @newExtensionSettings = STUFF(@newExtensionSettings, CHARINDEX(@OpenTag_EmailReplyTo, @newExtensionSettings ,1) + LEN(@OpenTag_EmailReplyTo),
CHARINDEX('</Value></ParameterValue>',@newExtensionSettings ,CHARINDEX(@OpenTag_EmailReplyTo, @newExtensionSettings ,1) + LEN(@OpenTag_EmailReplyTo))-(CHARINDEX(@OpenTag_EmailReplyTo, @newExtensionSettings ,1) + LEN(@OpenTag_EmailReplyTo)), @EmailReplyTo)
END
IF @newExtensionSettings LIKE '%<ParameterValue><Name>Comment</Name><Value>%'
BEGIN
SET @newExtensionSettings = STUFF(@newExtensionSettings, CHARINDEX(@OpenTag_EmailBody, @newExtensionSettings ,1) + LEN(@OpenTag_EmailBody),
CHARINDEX('</Value></ParameterValue>',@newExtensionSettings ,CHARINDEX(@OpenTag_EmailBody, @newExtensionSettings ,1) + LEN(@OpenTag_EmailBody))-(CHARINDEX(@OpenTag_EmailBody, @newExtensionSettings ,1) + LEN(@OpenTag_EmailBody)), @EmailBody)
END
-- Temporarily update the values
UPDATE ReportServer.dbo.Subscriptions
SET [ExtensionSettings] = CAST(@newExtensionSettings AS ntext),
[Parameters] = CAST(@newParameters AS ntext)
WHERE SubscriptionID = @subscriptionID;
-- run the job
EXEC msdb..sp_start_job @job_name = @ScheduleID
-- make enough delay for the report to have started
WAITFOR DELAY '00:00:20'
-- put the original extensionsettings and parameter values back
UPDATE ReportServer.dbo.Subscriptions
SET [ExtensionSettings] = CAST(@originalExtensionSettings AS ntext),
[Parameters] = CAST(@originalParameters AS ntext)
WHERE SubscriptionID = @subscriptionID;
-- finally we free up the subscription system for another person to use
DROP TABLE ##ReportInUse;
SET NOCOUNT OFF;
END;
Building and Passing through the Parameters
Below is the script to pass through the parameters to the main stored procedure. Each parameter needs to be declared with both the parameter name from the SSRS report and the value you intend on using. These will be @Param_1 etc and @Param_1_Value etc
BEGIN
IF OBJECT_ID('tempdb.dbo.#Parameter_Details') IS NOT NULL DROP TABLE #Parameter_Details
DECLARE @Parameter_Details TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, Param_Name VARCHAR(MAX), Param_Values VARCHAR(MAX))
DECLARE @Loop_ID INT, @Working_Param_1 VARCHAR(MAX), @Working_Param_1_Value VARCHAR(MAX), @Parameters_Final VARCHAR(MAX), @Comments VARCHAR(8000),
@Param_1 VARCHAR(MAX) = 'Parameter_1',
@Param_2 VARCHAR(MAX) = 'Parameter_2',
@Param_3 VARCHAR(MAX) = 'Parameter_3',
@Param_1_Value VARCHAR(MAX) = CONVERT(VARCHAR(10), DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0)), 111) + ' 12:00:00 AM',
@Param_2_Value VARCHAR(MAX) = CONVERT(VARCHAR(10), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)), 111) + ' 12:00:00 AM',
@Param_3_Value VARCHAR(MAX) = 'TEXT'
SET @Comments = 'Add' + CHAR(10) + CHAR(10) + 'Your text for the email body' + CHAR(10) + CHAR(10) + 'Here'
INSERT INTO @Parameter_Details VALUES (@Param_1, @Param_1_Value), (@Param_2, @Param_2_Value), (@Param_3, @Param_3_Value)
SET @Parameters_Final = ''
SELECT @Loop_ID = MIN(ID) FROM @Parameter_Details
WHILE @Loop_ID IS NOT NULL
BEGIN
IF OBJECT_ID('tempdb.dbo.#Param_tmp') IS NOT NULL DROP TABLE #Param_tmp
SELECT @Working_Param_1 = Param_Name FROM @Parameter_Details WHERE ID = @Loop_ID
SELECT @Working_Param_1_Value = Param_Values FROM @Parameter_Details WHERE ID = @Loop_ID
SELECT ROW_NUMBER() OVER (ORDER BY ITEM) AS ID, Item AS Params
INTO #Param_tmp
FROM dbo.SplitStrings(@Working_Param_1_Value, ',')
SET @Parameters_Final += REPLACE(REPLACE(REPLACE(Stuff((SELECT N' <' + '<ParameterValue><Name>' + @Working_Param_1 + '</Name><Value>' + Params + '</Value></ParameterValue>' FROM #Param_tmp FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N''), ' <', '<'), '><<', '><'), '<Value> ', '<Value>')
SELECT @Loop_ID = MIN(ID) FROM @Parameter_Details WHERE ID > @Loop_ID
END
SELECT @Parameters_Final = '<ParameterValues>' + @Parameters_Final + '</ParameterValues>'
EXEC dbo.ExecuteSubscribedReport
@ScheduleID = '9E9C915C-5088-418E-B0BB-E4EE1A93A1EE',
@EmailTo = 'Email_TO@address.com',
@EmailCC = 'Email_CC@address.com',
@EmailBCC = 'Email_BCC@address.com',
@EmailReplyTo = 'Email_Reply@address.com',
@EmailBody = @Comments,
@ParameterList = @Parameters_Final
ENDPlease let me know what you guys think of this solution?