SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Create SSRS Data Driven Subscriptions on Standard Edition

By Bernard Beckett,

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:

  1.     Create the Split String Function
  2.     Create the main Stored Procedure
  3.     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
END

Please let me know what you guys think of this solution?

 
Total article views: 799 | Views in the last 30 days: 799
 
Related Articles
FORUM

Stored Procedure with varchar(max) as parameter

Stored Procedure with varchar(max) as parameter

FORUM

Regarding using VARCHAR(MAX)

Usage of VARCHAR(MAX)

FORUM

Different behaviour of SQL Server when setting varchar size

When declaring parameters of varchar, setting size gives different query results

FORUM

varchar issue

varchar issue

FORUM

Tricky ...VARCHAR

VARCHAR logics

 
Contribute