Create SSRS Data Driven Subscriptions on Standard Edition

  • bernard.beckett

    SSC Veteran

    Points: 203

    Comments posted to this topic are about the item Create SSRS Data Driven Subscriptions on Standard Edition

  • epowell 61898

    SSC Enthusiast

    Points: 135

    Bernard,
    Good solution - we had the same issue with multiple clients but wanted a user capable driven solution that did SSRS, Crystal and Excel - we found  R-Tag Manager works well for users but that cost is $999 cheaper than the Enterprise addition and users love it - interface is a bit weird but one your used to it it works and you can "batch" solutions.
    I will use your solution for my canned stuff and for clients who wont spend money. 
    Thank you so much for sharing.

  • Thom A

    SSC Guru

    Points: 98507

    Not a great fan of looping through the parameters.

    It's also worth noting that if you have a subscription running, and try to run it again (at least with 2012-) then the old subscription will not be completed. This is a real pain if you have multiple events occur within a very short time period, as if one event starts before another finishes, the earlier report will not be submitted.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • cerulean47

    Valued Member

    Points: 61

    Thanks for this.

    What do you think about wrapping the report execution in a try/catch? I'd want to make sure if the report execution errors out for any reason, we get a chance to safely restore the original parameters.

  • Thom A

    SSC Guru

    Points: 98507

    cerulean47 - Tuesday, October 31, 2017 7:43 AM

    Thanks for this.

    What do you think about wrapping the report execution in a try/catch? I'd want to make sure if the report execution errors out for any reason, we get a chance to safely restore the original parameters.

    You wouldn't be able to do this with a TRY...CATCH. When you run an SSRS report using T-SQL, all you effectively do is mark the subscription to be run; the database engine doesn't do the work. If the report/subscription fails is a completely separate task.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • cerulean47

    Valued Member

    Points: 61

    Thom A - Tuesday, October 31, 2017 8:07 AM

    cerulean47 - Tuesday, October 31, 2017 7:43 AM

    Thanks for this.

    What do you think about wrapping the report execution in a try/catch? I'd want to make sure if the report execution errors out for any reason, we get a chance to safely restore the original parameters.

    You wouldn't be able to do this with a TRY...CATCH. When you run an SSRS report using T-SQL, all you effectively do is mark the subscription to be run; the database engine doesn't do the work. If the report/subscription fails is a completely separate task.

    I see. The timed delay makes me a little nervous. Is it possible to poll to determine if the report successfully started?

  • DeusExDatum

    Ten Centuries

    Points: 1144

    Wrote this for a client years ago, still running today.

    Couple of tips:
    1.) Handle the parameters and extension settings as the whole XML BLOB; don't chunk it up.  Code is much cleaner, and format is guaranteed to remain intact.


    --Not all variables declared for brevity, but you get the idea
    DECLARE
                @extensionSettings XML,
                @parameters XML

       SELECT
            @extensionSettings = ExtensionSettings,
            @parameters = [Parameters]
        FROM dbo.Subscriptions
        WHERE SubscriptionID = @subscriptionID;
    --Set Extension Options
        SET @extensionSettings.modify('replace value of (/ParameterValues/ParameterValue[Name="TO"]/Value[1]/text())[1] with sql:variable("@Email")');
        SET @extensionSettings.modify('replace value of (/ParameterValues/ParameterValue[Name="Subject"]/Value[1]/text())[1] with sql:variable("@emailSubject")');
        SET @extensionSettings.modify('replace value of (/ParameterValues/ParameterValue[Name="Comment"]/Value[1]/text())[1] with sql:variable("@BodyString")');
        
        --Set Report Options
        SET @parameters.modify('replace value of (/ParameterValues/ParameterValue[Name="ID"]/Value[1]/text())[1] with sql:variable("@ID")');
        SET @parameters.modify('replace value of (/ParameterValues/ParameterValue[Name="Month"]/Value[1]/text())[1] with sql:variable("@ReportMonth")');
       
        UPDATE dbo.Subscriptions
            SET
                ExtensionSettings = CAST(@extensionSettings AS nvarchar(max)),
                [Parameters] = CAST(@parameters AS nvarchar(max))
        WHERE SubscriptionID = @subscriptionID;

    2.) Don't rely on SQL Agent to execute;  it takes way longer, and job calls are async, so you need extra code deployed to check job status before you can change things and run the next iteration of the report.  You can inject Events directly to SSRS, and your procedure essentially fires all the iterations of the report instead of SSRS, based on the data driven set you put in the proc.

    --Write a loop or cursor around this and include your updates to the subscription

    --Queue the Report for execution with the parameter / extension changes you've made
    EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @subscriptionID;
        
        --Delay to ensure it is queued
        WAITFOR DELAY '00:00:01.500';
        
        --If the execution is still queued, or running, wait
        WHILE EXISTS (SELECT TOP 1 1 FROM dbo.Event WHERE EventData = @subscriptionID ) OR EXISTS (SELECT TOP 1 1 FROM dbo.Notifications WHERE SubscriptionID = @subscriptionID)
                        BEGIN
                            WAITFOR DELAY '00:00:01.000';
                        END

        --At this point, we would loop, set parameters / extensions on the subscription for the next iteration of the report, and run another AddEvent

    I run about 700 reports a month like this through 2 proc calls (PDF Export to fileshare, and Emailed); takes about 20 minutes and the two reports can run in parallel.  Definitely better than eating Enterprise licensing cost. 😉

    Josh Lewis

  • Ronzo

    Hall of Fame

    Points: 3741

    It is worth noting that if you are using SQL 2016 and above, you can use the built-in STRING_SPLIT function instead of "rolling your own". It is limited to single character separators, but is much faster and a lot less code to write/maintain.


    Have Fun!
    Ronzo

  • bernard.beckett

    SSC Veteran

    Points: 203

    Thom A - Tuesday, October 31, 2017 6:15 AM

    Not a great fan of looping through the parameters.

    It's also worth noting that if you have a subscription running, and try to run it again (at least with 2012-) then the old subscription will not be completed. This is a real pain if you have multiple events occur within a very short time period, as if one event starts before another finishes, the earlier report will not be submitted.

    Hi Thom,

    Thanks for your reply.

    Your comment above has also been catered for in the main stored proc with this piece of code, which will wait for one subscription to end before starting another:

    -- 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);

  • bernard.beckett

    SSC Veteran

    Points: 203

    Ronzo - Wednesday, November 1, 2017 10:59 AM

    It is worth noting that if you are using SQL 2016 and above, you can use the built-in STRING_SPLIT function instead of "rolling your own". It is limited to single character separators, but is much faster and a lot less code to write/maintain.

    Hi Ronzo,

    The new SPLT_STRING function is only available in this version of SQL: SQL Server 2016 RC0

  • nikimpatel

    Valued Member

    Points: 64

    epowell 61898 - Tuesday, October 31, 2017 6:05 AM

    Bernard,
    Good solution - we had the same issue with multiple clients but wanted a user capable driven solution that did SSRS, Crystal and Excel - we found  R-Tag Manager works well for users but that cost is $999 cheaper than the Enterprise addition and users love it - interface is a bit weird but one your used to it it works and you can "batch" solutions.
    I will use your solution for my canned stuff and for clients who wont spend money. 
    Thank you so much for sharing.

    Thanks for your help. I like "R-Tag Manager".

  • JSB_89

    Ten Centuries

    Points: 1041

    Bernard,

    Thnx for your solution , we did an upgrade from 2012 Developer to 2016 Standard . I have never did SSRS earlier (Data Driven subscriptions part). So i might need some help with this solution. I will get with you and thnx again for your solution .

  • bernard.beckett

    SSC Veteran

    Points: 203

    @JSB_89, it's a pleasure, and I'll gladly help where I can.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply