Data Driven Subscriptions in SQL RS 2000 Standard

• Download source - 1.57 Kb

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 Server 2000? The only solution available in the Standard edition, besides writing your own front end in .NET, is 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, Eureka! 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 a predefined text in the subscription and replace it with what you supply. It may not be as pretty as the version in the SQL Server Enterprise edition, but this one gets the job done, and it is very useful!

I have not included any error checking, and currently, this stored procedure 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.

How to use the code

1. Create a new subscription for the report that you want to set up a data driven subscription for. Use the following settings:

   To Address:            |TO|
   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 the ending date to a date prior to today.

   

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