Data Driven Subscriptions

  • Jason Selburg

    SSC-Insane

    Points: 24560

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Hugh Thomas

    Ten Centuries

    Points: 1017

    Great article!  I emailed Jason a suggestion for the delay, to maybe use the ModifiedDate and LastRunTime fields in the Subscriptions table to test if LastRun is greater than Modified, at which time you can move forward. I'm going to try that and see if it works.

  • Sunil Shah

    SSC Rookie

    Points: 28

    Excellent article !!  I can't wait to try it out.

    Sunil

  • Yelena Varshal

    SSC-Dedicated

    Points: 34207

    I use VBscript with Named Arguments where I pass the configuration file name. This configuration files contains 4 lines: TOaddresses, SubjectText, BodyText and Attachments. We do have to have 1 line batch files for different configuration files because I am trying to keep it simple for the person who manages these files. It could be replaced with one batch if you know you DOS programming well. This batch is just scheduled in Windows Task Scheduler.

    The calling batch:

    cscript "MyScriptThatSendsEmails.vbs" /ConfigFile:"TextFileContainingEmailStrings.txt"

    Code in the MyScriptThatSendsEmails.vbs has to contain the following lines in order to get the name of the configuration file.

    Set colNamedArguments = WScript.Arguments.Named

    ConfigFile = colNamedArguments.Item("ConfigFile")

    Then I use FileSystemObject to access lines in this file.

    The report itself is sent as an attachment. The report has to be pre-generated by any report tool and placed in the same directory each time for the script to be able to find it.

     

    Regards,Yelena Varsha

  • Jason Selburg

    SSC-Insane

    Points: 24560

    Thanks everyone for the comments.

    I have one quick update, replace the text "IF @length > 0" with "IF isNull(@length, 0) > 0" in each place in the procedure.

    Also, I am working on a more robust version of this approach and will post the new info.

     

    Thanks!

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • philcart

    SSC-Forever

    Points: 47792

    Good article, always handy to see alternative methods.

    I built a C# console app that renders the reports via the webservice. Once I got my head around the .Net stuff is was relatively easy to put together.

    The app takes a stored procedure name as one of its parameters and this stored procedure returns a DataReader which is used to drive the report rendering.

    One of the nice features of rendering via the webservice is that the same report can be rendered in any of the registered Reporting services formats and you can have 0-n parameters for the report.

     

    --------------------
    Colt 45 - the original point and click interface

  • Jason Selburg

    SSC-Insane

    Points: 24560

    I’m happy to say that I’ve made some major improvements to the code using many of your suggestions.

     

    Improvements:

    • A more efficient (and shorter) procedure that handles errors.
    • The procedure is commented better and should give you a MUCH better understanding of what is going on.
    • An improved method of “waiting” until the subscription completes before continuing.
    • The ability to handle multiple (almost unlimited) parameters.
    • *BIG* The ability to NAME your subscriptions and refer to them by name. (As it would get difficult to manage by schedule IDs)
    • More settings available on the subscription, like “include report/link, render format, etc…”

     

     

    If you are interested, reply and I’ll send the updated procedures and instructions. There is a replacement for the main procedure, one to gather and format the parameters and a table to hold them. Once in place, it’s quite easy to use.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Drew Williams

    SSC Rookie

    Points: 35

    Jason,

    I'm definitely interested in taking a look at your improvements, especially the improved method of waiting for subscriptions to finish.

  • grant whellum

    SSC Rookie

    Points: 27

    Jason,

    Works great except for the waiting for the report to finish code. Can you post this update please.

  • Jason Selburg

    SSC-Insane

    Points: 24560

    To address the wait time for the subscription to complete, make the following changes to the stored proc. This solution was devised by hughthomas -> http://www.sqlservercentral.com/forums/userinfo.aspx?id=86254

     

    Add the following lines to the end variable declaration section ...

     @starttime datetime,

     @lastruntime datetime

    set @starttime = DATEADD(second, -2, getdate())

    set @execTime = getdate()

    Now replace the "WAITFOR DELAY '00:00:10' " with ...

       -- this give the report server time to execute the job

       SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID

       While (@starttime > @lastruntime)

       Begin

        print '...'

        print @lastruntime

        WAITFOR DELAY '00:00:03'

        SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID

       End

     

     

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jason Selburg

    SSC-Insane

    Points: 24560

    Here is a revised version that works much more efficiently, and addresses various issues with the original version.

    ---------------------------------------------------------------------------------------------------

    USE [ReportServer]

    GO

    /****** Object: StoredProcedure [dbo].[js_data_driven_subscription] Script Date: 09/29/2006 18:10:40 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE procedure [dbo].[data_driven_subscription]

    ( @scheduleID uniqueidentifier,

    @emailTO varchar (2000) = ' ',

    @emailCC varchar (2000) = ' ',

    @emailBCC varchar (2000) = ' ',

    @emailReplyTO varchar (2000) = ' ',

    @emailBODY varchar (8000) = ' ',

    @paramValue1 varchar (256) = ' ',

    @paramName1 varchar(8000),

    @sub varchar(1000),

    @renderFormat varchar(50) = 'PDF'

    )

    as

    DECLARE

    @ptrval binary(16),

    @PARAMptrval binary(16),

    @subscriptionID uniqueidentifier,

    @starttime datetime,

    @lastruntime datetime,

    @dValues varchar (8000),

    @pValues varchar (8000)

    set @starttime = DATEADD(second, -2, getdate())

    set @emailTO = rtrim(@emailTO)

    set @emailCC = rtrim(@emailCC)

    set @emailBCC = rtrim(@emailBCC)

    set @emailReplyTO = rtrim(@emailReplyTO)

    set @emailBODY = rtrim(@emailBODY)

    set @paramValue1 = rtrim(@paramValue1)

    -- set the subscription ID

    SELECT @subscriptionID = SubscriptionID

    FROM ReportSchedule WHERE ScheduleID = @scheduleID

    set @dValues = ''

    set @pValues = ''

    if IsNull(@emailTO, '') ''

    set @dValues = @dValues + 'TO' + @emailTO + ''

    if IsNull(@emailCC, '') ''

    set @dValues = @dValues + 'CC' + @emailCC + ''

    if IsNull(@emailBCC, '') ''

    set @dValues = @dValues + 'BCC' + @emailBCC + ''

    if IsNull(@emailReplyTO, '') ''

    set @dValues = @dValues + 'ReplyTo' + @emailReplyTO + ''

    if IsNull(@emailBODY, '') ''

    set @dValues = @dValues + 'Comment' + @emailBODY + ''

    if IsNull(@sub, '') ''

    set @dValues = @dValues + 'Subject' + @sub + ''

    if IsNull(@dValues, '') ''

    set @dValues = '' + @dValues +

    'IncludeReportTrue'

    if IsNull(@dValues, '') ''

    set @dValues = @dValues + 'RenderFormat' +

    @renderFormat + '' +

    'IncludeLinkFalse'

    if IsNull(@paramName1, '') '' and IsNull(@paramValue1, '') ''

    set @pValues = '' +

    @paramName1 +

    '' +

    @paramValue1 +

    ''

    if IsNull(@dValues, '') '' and IsNull(@pValues, '') ''

    BEGIN

    update Subscriptions set extensionsettings = '' WHERE SubscriptionID = @SubscriptionID

    update Subscriptions set parameters = '' WHERE SubscriptionID = @SubscriptionID

    -- set the text point for this record

    SELECT @ptrval = TEXTPTR(ExtensionSettings)

    FROM Subscriptions WHERE SubscriptionID = @SubscriptionID

    UPDATETEXT Subscriptions.ExtensionSettings

    @ptrval

    null

    null

    @dValues

    -- set the text point for this record

    SELECT @PARAMptrval = TEXTPTR(Parameters)

    FROM Subscriptions WHERE SubscriptionID = @SubscriptionID

    UPDATETEXT Subscriptions.Parameters

    @PARAMptrval

    null

    null

    @pValues

    -- run the job

    exec msdb..sp_start_job @job_name = @scheduleID

    -- this give the report server time to execute the job

    SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID

    While (@starttime > @lastruntime)

    Begin

    print '...'

    print @lastruntime

    WAITFOR DELAY '00:00:03'

    SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID

    End

    END

    ---------------------------------------------------------------------------------------------------

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jason Selburg

    SSC-Insane

    Points: 24560

    Heads Up!

    A new and greatly improved procedure/article is coming up (within a few days). I have found that this procedure also works with RS 2005 and the error-trapping has been greatly improved.

    One thing that is not addressed is the ability to pass multiple parameters. Quite honestly, there are too many ways to attack that problem and it really depends on your environment and needs. So you'll have to take that task on yourselves....

    Another feature not addressed is the "File Share" delivery method. The username and password encryption currently has me stumped, but I'll continue to work on it.

    Thanks,

    Jason

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jason Selburg

    SSC-Insane

    Points: 24560

    Hi all,

    The new article won't be published until early March. But until then you can go here http://www.sqlservercentral.com/columnists/jselburg/2824.asp

    Just remember to vote when the article comes out in the newsletter *grin*

    Thanks

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • rlopez

    Newbie

    Points: 7

    The subscription code is great I am interested in the updated code. I am having a problem however, where the subscription works a few times and then stops and I get the message below when I try to view the subscriptions in the Report Manager. Thank you.

    An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help For more information about this error navigate to the report server on the local server machine, or enable remote errors

  • Shalu Goel

    Valued Member

    Points: 55

    Hi there,

    I'm interested in the new and improved data driven subscriptions solution. Is it still available?

    Thanks,

    Shalu

Viewing 15 posts - 1 through 15 (of 48 total)

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