Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Data Driven Subscriptions

By Jason Selburg,

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:
  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 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

Total article views: 20527 | Views in the last 30 days: 64
 
Related Articles
FORUM

Topic Subscriptions Select All

Topic Subscriptions Select All

FORUM

How to Specify Length of Output Column in SELECT Stmt?

How to Specify Length of Output Column in SELECT Stmt?

FORUM

Personal Subscription E-mail Address

Defaults to user name not SMTP address

BLOG

Updating Email Addresses in SSRS Subscriptions

Creating subscriptions in SQL Server Reporting Services is a great way to distribute files to end us...

FORUM

Script to execute the Reporting services Subscription

Reporting services Subscriptions

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones