Data Driven Subscriptions without SQL Enterprise

  • Jason Selburg

    SSC-Insane

    Points: 24560

    Introduction

    Do you have SQL Reports that need to go to different people but each person's report has different parameters? Don't have the resources to purchase the Enterprise edition of SQL 2000? The only solution available in Standard edition, besides writing your own front end in .NET was to create a subscription for each unique report, person and parameter. After only a short amount of time, you've got a ton of subscriptions to manage.

    Well, that's the situation I was in, and it was quickly becoming a nightmare managing all of the subscription change requests. I started thinking "Reporting Services is just a regular ol' database with an ASP.NET front end. There's got to be a way to do it..."

    Well, EURIKA! It seems the subscription information is stored in a table named, of all things "Subscriptions" and the parameter information is stored in, Yep, "Parameters". ... Those tricky Microsoft guys!

    Anyway I wrote a stored procedure that will look for predefined text in the subscription and replace it with what you supply. It may not be as pretty as the version in SQL Enterprise, but this one gets the job done, and it is very useful!

    I have not included any error checking and currently this SP only allows for one parameter. Feel free to modify the code as you see fit. Any suggestions or comments are welcome, email me.

    Thank you and I hope this helps or at least gives you ideas on where to go next.

    Instructions

    1. Create the stored procedure. Shown below.
    2. Create a new subscription for the report that you want to set up the data driven subscription for. Use the following settings:
    3. To Address: |TO| (pipe + TO + pipe)

      Carbon Copy Address: |CC|

      Blind Copy Address: |BC|

      Reply To Address: |RT|

      Comment / Body: |BD|

      Parameter 1: |P1|

    4. Now set the schedule of the report to run once.
    5. Set the beginning date and ending date to a date prior to today to keep the report from running unless you call it.
    6. In Enterprise Manager find the job you just created. It will have a Last Run Status of “Unknown” and the Next Run Date will be “Date and time are not available”. This job's name is what you’ll need to pass to the stored procedure as the Schedule ID. You can also run the query below to list the Job Names directly from the Report Server Database. Your job should be the first one listed.

    7. SELECT ReportSchedule.ScheduleID, Subscriptions.ModifiedDate

      FROM Subscriptions INNER JOIN

      ReportSchedule ON Subscriptions.SubscriptionID = ReportSchedule.SubscriptionID

      WHERE (Subscriptions.Description LIKE N'%|TO|%')

      ORDER BY Subscriptions.ModifiedDate DESC


    8. To execute the subscription, simply call the stored procedure passing the parameter values.

    Conclusion

    Now you are ready to call your procedure from within your code.

    Again, any comments, suggestions or improvements are welcome, email me.


    Stored Procedure


    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS OFF

    GO

    /*

    PROCEDURE DESCRIPTION:

    This procedure will replace the predefined fields for a SQL Reporting Services

    Subscription allowing a "DATA DRIVEN SUBSCRIPTION"

    INPUT:

    @scheduleID The Job Name in SQL Server 2000

    @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

    @param1 The value of the parameter defined as P1 in the subscription

    OUTPUT:

    None

    WRITTEN BY:

    Jason L. Selburg

    HeroTheCat@indy.rr.com

    NOTES:

    This procedure does not have error checking or return codes included.

    Feel free to modify this code as you see fit.

    */

    CREATE procedure dbo.usp_data_driven_subscription

    ( @scheduleID uniqueidentifier,

    @emailTO varchar (2000) = '',

    @emailCC varchar (2000) = '',

    @emailBCC varchar (2000) = '',

    @emailReplyTO varchar (2000) = '',

    @emailBODY varchar (8000) = '',

    @param1 varchar (256) = ''

    )

    as

    DECLARE

    @ptrval binary(16),

    @PARAMptrval binary(16),

    @TOpos int,

    @CCpos int,

    @BCCpos int,

    @RTpos int,

    @BODYpos int,

    @PARAM1Pos int,

    @length int,

    @subscriptionID uniqueidentifier

    -- set the subscription ID

    SELECT @subscriptionID = SubscriptionID

    FROM ReportSchedule WHERE ScheduleID = @scheduleID

    -- set the text point for this record

    SELECT @ptrval = TEXTPTR(ExtensionSettings)

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    -- set the text point for this record

    SELECT @PARAMptrval = TEXTPTR(Parameters)

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    -- set the start position for the TO Address

    SELECT @TOpos = patindex('%|TO|%', ExtensionSettings) - 1

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    IF isnull(@TOpos, '') <> '' and @TOpos > 0 and len(@emailTo) > 0

    -- change the TO address

    UPDATETEXT Subscriptions.ExtensionSettings

    @ptrval

    @TOpos

    4

    @emailTo

    -- set the start position for the CC Address

    SELECT @CCpos = patindex('%|CC|%', ExtensionSettings) - 1

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    IF isnull(@CCpos, '') <> '' and @CCpos > 0 and len(@emailCC) > 0

    -- change the TO address

    UPDATETEXT Subscriptions.ExtensionSettings

    @ptrval

    @CCpos

    4

    @emailCC

    -- set the start position for the BCC Address

    SELECT @BCCpos = patindex('%|BC|%', ExtensionSettings) - 1

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    IF isnull(@BCCpos, '') <> '' and @BCCpos > 0 and len(@emailBCC) > 0

    -- change the TO address

    UPDATETEXT Subscriptions.ExtensionSettings

    @ptrval

    @BCCpos

    4

    @emailBCC

    -- set the start position for the REPLY TO Address

    SELECT @RTpos = patindex('%|RT|%', ExtensionSettings) - 1

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    IF isnull(@RTpos, '') <> '' and @RTpos > 0 and len(@emailReplyTO) > 0

    -- change the REPLY TO address

    UPDATETEXT Subscriptions.ExtensionSettings

    @ptrval

    @RTpos

    4

    @emailReplyTO

    -- set the start position for the BODY Text

    SELECT @BODYpos = patindex('%|BD|%', ExtensionSettings) - 1

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    IF isnull(@BODYpos, '') <> '' and @BODYpos > 0 and len(@emailBODY) > 0

    -- change the REPLY TO address

    UPDATETEXT Subscriptions.ExtensionSettings

    @ptrval

    @BODYpos

    4

    @emailBODY

    -- set the start position for the Parameter 1

    SELECT @PARAM1Pos = patindex('%|P1|%', Parameters) - 1

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    IF isnull(@PARAM1Pos, '') <> '' and @PARAM1Pos > 0 and len(@param1) > 0

    -- change the Parameter 1 value

    UPDATETEXT Subscriptions.Parameters

    @PARAMptrval

    @PARAM1Pos

    4

    @param1

    -- run the job

    exec msdb..sp_start_job @job_name = @scheduleID

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

    WAITFOR DELAY '00:00:10'

    -- set the start position for the TO Address

    SELECT @TOpos = patindex('%' + @emailTO + '%', ExtensionSettings) - 1

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    SELECT @length = len(@emailTO)

    IF @length > 0

    -- replace the addresses with the original |TO|

    UPDATETEXT Subscriptions.ExtensionSettings

    @ptrval

    @TOpos

    @length

    '|TO|'

    -- set the start position for the TO Address

    SELECT @CCpos = patindex('%' + @emailCC + '%', ExtensionSettings) - 1

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    SELECT @length = len(@emailCC)

    IF @length > 0

    -- replace the addresses with the original |CC|

    UPDATETEXT Subscriptions.ExtensionSettings

    @ptrval

    @CCpos

    @length

    '|CC|'

    -- set the start position for the TO Address

    SELECT @BCCpos = patindex('%' + @emailBCC + '%', ExtensionSettings) - 1

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    SELECT @length = len(@emailBCC)

    IF @length > 0

    -- replace the addresses with the original |BC|

    UPDATETEXT Subscriptions.ExtensionSettings

    @ptrval

    @BCCpos

    @length

    '|BC|'

    -- set the start position for the REPLY TO Address

    SELECT @RTpos = patindex('%' + @emailReplyTO + '%', ExtensionSettings) - 1

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    SELECT @length = len(@emailReplyTO)

    IF @length > 0

    -- replace the addresses with the original |RT|

    UPDATETEXT Subscriptions.ExtensionSettings

    @ptrval

    @RTpos

    @length

    '|RT|'

    -- set the start position for the BODY Text

    SELECT @BODYpos = patindex('%' + @emailBODY + '%', ExtensionSettings) - 1

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    SELECT @length = len(@emailBODY)

    IF @length > 0

    -- replace the addresses with the original |RT|

    UPDATETEXT Subscriptions.ExtensionSettings

    @ptrval

    @BODYpos

    @length

    '|BD|'

    -- set the start position for the Parameter

    SELECT @PARAM1Pos = patindex('%' + @param1 + '%', Parameters) - 1

    FROM Subscriptions WHERE SubscriptionID = @subscriptionID

    SELECT @length = len(@param1)

    IF @length > 0

    -- replace the addresses with the original |P1|

    UPDATETEXT Subscriptions.Parameters

    @PARAMptrval

    @PARAM1Pos

    @length

    '|P1|'

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO


    ______________________________________________________________________

    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
  • This was removed by the editor as SPAM

  • Sheckster

    Old Hand

    Points: 351

    Thanks Jason, this will come in handy for me soon

  • Gloriann

    Ten Centuries

    Points: 1082

     

    This came in handy for me and it works beautifully!!! Jason you’re the man, thanks for all your help! 

      

  • Catherine Agacinski

    SSC Journeyman

    Points: 80

    This saved me a lot of trouble. I had a data-driven subscription working on my local server. We got a new firewall and it quit working.

    The production server can send email through the firewall but doesn't have the Enterprise edition of SQL.

    For the newbies like me:

    1. usp_data_driven_subscription goes in the database called ReportServer

    2. I called usp_data_driven_subscription from VB.NET inside a copied try-catch block, but had trouble debugging until I remembered that

    ex.ToString tells more than ex.Message (ex is my exception)

    3. I had to pass 2 integer parameters instead of 1 text parameter. RS did not like the pipe characters, so I put in 123456789 and 987654321 for the UPDATETEXT commands. They ranged from 1-digit to 3-digit integers, with lots of opportunity for the same digits in the parameters. Instead of trying to replace my integers with the placemarkers at the end of the loop, I put the whole original Parameter string back. It's a big XML string.

    -- put the original Parameters back

    UPDATETEXT Subscriptions.Parameters

    @PARAMptrval

    0

    NULL

    '[ParameterValues][ParameterValue][Name]cu[/Name][Value]123456789[/Value][/ParameterValue][ParameterValue][Name]sw[/Name][Value]987654321[/Value][/ParameterValue][/ParameterValues]'

    (replace square brackets with angles)

  • Jason Selburg

    SSC-Insane

    Points: 24560

    I've been meaning to post this for quite a while, but here's an updated procedure that works much better....

     

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[data_driven_subscription]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[data_driven_subscription]

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS 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) = ' ',

       @param1 varchar (256) = ' ',

       @p1 varchar(8000),

       @sub varchar(1000),

          @renderFormat varchar(50) = 'PDF'

    &nbsp

    as

    DECLARE

     @ptrval binary(16),

     @PARAMptrval binary(16),

     @TOpos int,

     @CCpos int,

     @BCCpos int,

     @RTpos int,

        @BODYpos int,

     @PARAM1Pos int,

     @length int,

     @subscriptionID uniqueidentifier,

     @job_status int,

     @I int, -- the rest were added by hugh 

     @starttime datetime,

     @lastruntime datetime,

     @execTime datetime,

     @dValues varchar (8000),

     @pValues varchar (8000)

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

    set @job_status = 1

    set @I = 1

    set @emailTO = rtrim(@emailTO)

    set @emailCC = rtrim(@emailCC)

    set @emailBCC = rtrim(@emailBCC)

    set @emailReplyTO = rtrim(@emailReplyTO)

    set @emailBODY = rtrim(@emailBODY)

    set @param1 = rtrim(@param1)

     -- set the subscription ID

     SELECT @subscriptionID = SubscriptionID

     FROM ReportSchedule WHERE ScheduleID = @scheduleID

     

     

     set @dValues = ''

     set @pValues = ''

     if IsNull(@emailTO, '') <> '' 

      set @dValues = @dValues + '<ParameterValue><Name>TO</Name><Value>' + @emailTO + '</Value></ParameterValue>' 

      

     if IsNull(@emailCC, '') <> '' 

      set @dValues = @dValues + '<ParameterValue><Name>CC</Name><Value>' + @emailCC + '</Value></ParameterValue>' 

      

     if IsNull(@emailBCC, '') <> '' 

      set @dValues = @dValues + '<ParameterValue><Name>BCC</Name><Value>' + @emailBCC + '</Value></ParameterValue>'

      

     if IsNull(@emailReplyTO, '') <> '' 

      set @dValues = @dValues + '<ParameterValue><Name>ReplyTo</Name><Value>' + @emailReplyTO + '</Value></ParameterValue>'

      

     if IsNull(@emailBODY, '') <> '' 

      set @dValues = @dValues + '<ParameterValue><Name>Comment</Name><Value>' + @emailBODY + '</Value></ParameterValue>'

     if IsNull(@sub, '') <> '' 

      set @dValues = @dValues + '<ParameterValue><Name>Subject</Name><Value>' + @sub + '</Value></ParameterValue>' 

      

     if IsNull(@dValues, '') <> ''

      set @dValues = '<ParameterValues>' + @dValues +

          '<ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue>'

     if IsNull(@dValues, '') <> ''

      set @dValues = @dValues + '<ParameterValue><Name>RenderFormat</Name><Value>' +

          @renderFormat + '</Value></ParameterValue>' + 

          '<ParameterValue><Name>IncludeLink</Name><Value>False</Value></ParameterValue></ParameterValues>'

     

     if IsNull(@p1, '') <> '' and IsNull(@param1, '') <> ''

      set @pValues = '<ParameterValues><ParameterValue><Name>' +

          @p1 +

          '</Name><Value>' +

          @param1 +

          '</Value></ParameterValue></ParameterValues>'

     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

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

     

    ______________________________________________________________________

    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
  • Andy Warren

    SSC Guru

    Points: 119675

    I haven't tried it yet, but it looks like a decent solution - perhaps decent is modest considering the cost of moving to Enterprise edition. One thing you might want to add support for is the chance that any of the data points you're passing may have a symbol you need to escape out. For example, maybe someone builds a report that has <VALUE> in the subject. If you get some bad tags you'll wind up with a malformed XML doc that probably wont execute correctly. Probably not hard to track down, but also probably going to happen on the day when you just don't have time for it!

  • Jason Selburg

    SSC-Insane

    Points: 24560

    Thanks Andy. I guess I hadn't thought of that as I developed this to solve an in house need and that wasn't somethingI have to deal with. but that is a very good point.

    ______________________________________________________________________

    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
  • uday-489097

    SSC Journeyman

    Points: 75

    Hi Jason,

    I have used your store procedure.and it works fine.

    But I have one problem I need to send my Report to multiple users based on different parameter values.

    for example if parameter value is 100 then Report will send to abc@xyz.com

    if parameter value is 200 then Report will send to aaa@xyz.com

    if parameter value is 300 theb Report will send to bbb@xyz.com

    Using this store proceduer it is not possible.can you please let me know what I need to modify in this

    store proceduer or do i need to create any new sp?

    Thanks.

     

     

     

     

     

     

     

  • wijn

    Valued Member

    Points: 62

    Thank you for this one Mr. Selburg, especially the improved one that allows for saving the report to file.

    Well done!

  • Marcus-269395

    SSC Journeyman

    Points: 97

    This really helped me to schedule reports , I have sql server 2012 std version ,

  • sanket.sawant

    SSC Veteran

    Points: 262

    Hi Sir,

    I am using SQL Server 2008 R2 & using your SP to achieve what I want. what I am trying to achieve is, I am working on Loan Mortgage system. whenever status of any loan changes I want to mail details of that loan in predefined format to one specific Email Id. I tried to call your SP. I passed Loan Number through @param1 in your SP. which in turn gives me the desired output i.e. sending mail with desired Loan Number.

    Initially it worked fine, giving me desired output but now when i use same SP on same server without single change it is not working. whenever mail is fired it sends blank PDF without any data but just formatting of the report. I think it is not able to catch the value for the parameter. Where am i going wrong?? Please kindly reply at earliest.

    Many thanks in advance.

  • sanket.sawant

    SSC Veteran

    Points: 262

    here what should we pas in Report parameter Values textbox on subscription page of report manager??

  • sanket.sawant

    SSC Veteran

    Points: 262

    I am still getting blank pdf file. I tried both the versions that you have mentioned in this blog but I am not getting output that i want. 🙁

  • sanket.sawant

    SSC Veteran

    Points: 262

    Does anybody have any solution for this issue??

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

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