Data Driven Subscriptions in SQL RS Standard
As many of you know, data driven subscriptions is not a feature available with SQL 2000 RS Standard Edition. However, you can accomplish this using the supplied stored procedure. 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 error checking and this SP only allows for one parameter. Feel free to modify the code as you see fit. Any suggestions or comments are welcome through this site or email me at HeroTheCat@indy.rr.com
Thank you, and I hope this helps....
1. Create a new subscription for the report that you want to set up a data driven subscription for. You can set the render type and delivery method however you want but you must 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|
2. Now set the schedule of the report to run once.
3. Set the beginning date and ending date to a date prior to today to prevent the subscription from running unless you call it.
4. Retrieve the Job name from the SQL RS Database with the query below. Your subscription should be the first in the result set.
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
5. To execute the subscription, simply call the stored procedure passing your values. Only the @scheduleID and @emailTO are required.
EXEC dbo.uspdata_driven_subscriptions
@scheduleID = '',
@emailTO = '',
@emailCC = '',
@emailBCC = '',
@emailReplyTO = '',
@emailBODY = '',
@param1 = ''
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
