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

Rate

5 (1)

Share

Share

Rate

5 (1)