Geoff's Business Intelligence Blog: SSRS

Recently I was asked to come up with a solution where we could emulate the usefulness of a data-driven subscription without paying out for the Enterprise edition of SQL Server. This was the result.

The idea is to create an ‘empty’ standard subscription in SSRS and then, using a stored procedure, manipulate the parameters and run the report.
First create a dummy subscription that isn’t scheduled to run:

Set the parameters and delivery information as above. For now we will use only one parameter, but the stored procedure can easily be changed to accommodate as many as required.

Once the subscription is saved, you need to get the SubscriptionID from the report server. Use the following code:

SELET

c.Name AS ReportName,

rs.ScheduleID

FROM ReportServer.dbo.[Catalog] c

INNER JOIN ReportServer.dbo.Subscriptions s ON c.ItemID = s.Report_OID

INNER JOIN ReportServer.dbo.ReportSchedule rs ON c.ItemID = rs.ReportID

AND rs.SubscriptionID = s.SubscriptionID

Where

c.Name = 'Subscription Name'

You’ll need the SubscriptionID when calling the report.

Next create the following stored procedure that will be called to run the report(s):

(I found this SP on the internet, but can’t remember where so can’t credit where it’s due), either way it has a couple of changes and I hope it helps out other people)

USE [ReportServer]

/****** Object: StoredProcedure [dbo].[data_driven_subscription] Script Date: 04/11/2012 16:38:33 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER procedure [dbo].[data_driven_subscription_1_Param]

( @scheduleID uniqueidentifier,

@TO varchar (2000) = ' ',

@CC varchar (2000) = ' ',

@BCC varchar (2000) = ' ',

@ReplyTO varchar (2000) = ' ',

@BODY varchar (8000) = ' ',

@param1 varchar (256) = ' '

)

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 records Email info
       SELECT @ptrval = TEXTPTR(ExtensionSettings)
       FROM Subscriptions WHERE SubscriptionID = @subscriptionID

       -- set the text point for this records Parameter info
       SELECT @PARAMptrval = TEXTPTR(Parameters)
       FROM Subscriptions WHERE SubscriptionID = @subscriptionID

              -- set the start position for the TO Address
              SELECT @TOpos = patindex('%|EmailTO|%', ExtensionSettings) - 1
              FROM Subscriptions WHERE SubscriptionID = @subscriptionID


                     IF isnull(@TOpos, '') <> '' and @TOpos > 0 and len(@To) > 0
                           -- change the TO address
                           UPDATETEXT Subscriptions.ExtensionSettings
                                   @ptrval
                                  @TOpos
                                  4
                                  @To

              -- set the start position for the CC Address
              SELECT @CCpos = patindex('%|CarbonCopy|%', ExtensionSettings) - 1
              FROM Subscriptions WHERE SubscriptionID = @subscriptionID


                     IF isnull(@CCpos, '') <> '' and @CCpos > 0 and len(@CC) > 0
                           -- change the TO address
                           UPDATETEXT Subscriptions.ExtensionSettings
                                  @ptrval
                                  @CCpos
                                  4
                                  @CC

              -- set the start position for the BCC Address
              SELECT @BCCpos = patindex('%|BlindCopy|%', ExtensionSettings) - 1
              FROM Subscriptions WHERE SubscriptionID = @subscriptionID


                     IF isnull(@BCCpos, '') <> '' and @BCCpos > 0 and len(@BCC) > 0
                           -- change the TO address
                           UPDATETEXT Subscriptions.ExtensionSettings
                                  @ptrval
                                  @BCCpos
                                  4
                                  @BCC

              -- set the start position for the REPLY TO Address
              SELECT @RTpos = patindex('%|ReplyTo|%', ExtensionSettings) - 1
              FROM Subscriptions WHERE SubscriptionID = @subscriptionID


                     IF isnull(@RTpos, '') <> '' and @RTpos > 0 and len(@ReplyTO) > 0
                           -- change the REPLY TO address
                           UPDATETEXT Subscriptions.ExtensionSettings
                                  @ptrval
                                  @RTpos
                                  4
                                  @ReplyTO

-- set the start position for the BODY Text

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

FROM Subscriptions WHERE SubscriptionID = @subscriptionID

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

-- change the REPLY TO address

UPDATETEXT Subscriptions.ExtensionSettings

@ptrval

@BODYpos

@BODY

-- set the start position for the Parameter 1

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

FROM Subscriptions WHERE SubscriptionID = @subscriptionID

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

-- change the Parameter 1 value

UPDATETEXT Subscriptions.Parameters

@PARAMptrval

@PARAM1Pos

@parameter1

-- run the job

exec msdb..sp_start_job @job_name = @scheduleID

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

-- there is probably a better way to do this, so let me know if you know it ...

WAITFOR DELAY '00:00:10'

-- now change everything back so you can run this again

-- set the start position for the TO Address

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

FROM Subscriptions WHERE SubscriptionID = @subscriptionID

SELECT @length = len(@TO)

IF @length > 0

-- replace the addresses with the original |TO|

UPDATETEXT Subscriptions.ExtensionSettings

@ptrval

@TOpos

@length

'|EmailTO|'

-- set the start position for the TO Address

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

FROM Subscriptions WHERE SubscriptionID = @subscriptionID

SELECT @length = len(@CC)

IF @length > 0

-- replace the addresses with the original |CC|

UPDATETEXT Subscriptions.ExtensionSettings

@ptrval

@CCpos

@length

'|CarbonCopy|'

-- set the start position for the TO Address

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

FROM Subscriptions WHERE SubscriptionID = @subscriptionID

SELECT @length = len(@BCC)

IF @length > 0

-- replace the addresses with the original |BC|

UPDATETEXT Subscriptions.ExtensionSettings

@ptrval

@BCCpos

@length

'|BlindCopy|'

-- set the start position for the REPLY TO Address

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

FROM Subscriptions WHERE SubscriptionID = @subscriptionID

SELECT @length = len(@ReplyTO)

IF @length > 0

-- replace the addresses with the original |RT|

UPDATETEXT Subscriptions.ExtensionSettings

@ptrval

@RTpos

@length

'|ReplyTo|'

-- set the start position for the BODY Text

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

FROM Subscriptions WHERE SubscriptionID = @subscriptionID

SELECT @length = len(@BODY)

IF @length > 0

-- replace the addresses with the original |BD|

UPDATETEXT Subscriptions.ExtensionSettings

@ptrval

@BODYpos

@length

'|Comment|'

-- set the start position for the Parameter

SELECT @PARAM1Pos = patindex('%' + @parameter1 + '%', 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

'|Parameter1|'

You’ll notice the name of the SP ends with ‘1_Param’. To add more use the following code in the relevant part of the SP:

-- set the start position for the Parameter 2

              SELECT @PARAM2Pos = patindex('%|Parameter2|%', Parameters) - 1
              FROM Subscriptions WHERE SubscriptionID = @subscriptionID


             IF isnull(@PARAM2Pos, '') <> '' and @PARAM2Pos > 0 and len(@parameter2) > 0
                           -- change the Parameter 1 value
                           UPDATETEXT Subscriptions.Parameters
                                  @PARAMptrval
                                  @PARAM2Pos
                                  4
                                  @parameter2

                SELECT @length = len(@param2)
         IF @length > 0
                     -- replace the addresses with the original |P2|
                     UPDATETEXT Subscriptions.Parameters
                           @PARAMptrval
                           @PARAM1Pos
                           @length
                           '|Parameter2|'

It would be prudent to create a few versions of this SP with carrying numbers of parameters to meet all your needs.

The next SP is the one that runs and controls your new data-driven subscription capabilities.

declare @ReportParameter varchar(50)
declare @email_address varchar(255)
declare cur_cursor cursor for (select report_parameter,email_address

from server.[database].[dbo].[tablewith data])

open cur_cursor
Fetch Next from cur_cursor into @ReportParameter, @email_address
While @@fetch_status = 0
begin

exec data_driven_subscription_1_Param
@scheduleID = 'FE98A2D4-4430-4D5C-806C-717470CB1332',
@TO = @email_address,
@CC = '',
@BCC = 'AdministrativeEmail@CompanyName.com',
@ReplyTO = 'RelevantTeam@YourCompany.com',
@BODY = 'Hello testing 123',
@param1 = @ReportParameter

Fetch Next from cur_cursor into @ReportParameter, @email_address
end
close cur_cursor
deallocate cur_cursor

This cursor uses a table directly, but you can create any complicated query you wish to determinethe parameters and emails, just as you would with a normal data-driven subscription.

Notice the @scheduleID – this is the scheduleID you collected after you saved the standard report subscription.

The above code can either be turned into an SP, or placed directly into a SQL Agent Job.

Once you have an Agent job to run this procedure you can schedule it when and as required.

Any questions please don’t hesitate to contact me.

Geoff's Business Intelligence Blog

Wednesday, 11 April 2012

Data-Driven Subcriptions in Standard Edition of SQL Server