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. Below.
2. Create a new subscription for the report that you want to set up the data driven subscription for. Use the following settings:

	To Address:	|TO| (pipe + TO + pipe)
	Carbon Copy Address:	|CC|
	Blind Copy Address:	|BC|
	Reply To Address:	|RT|
	Comment / Body:	|BD|
	Parameter 1:	|P1|



3. Now set the schedule of the report to run once.



4. Set the beginning date and ending date to a date prior to today to keep the report from running unless you call it.



5. 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.

---

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

---

6. To execute the subscription, simply call the stored procedure passing these 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

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) = ' '
)
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 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('%|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.
-- 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('%' + @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 |BD|
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