Click here to Skip to main content
15,030,954 members
Articles / Database Development / SQL Server
Posted 12 May 2006


21 bookmarked

Data Driven Subscriptions in SQL RS 2000 Standard

Rate me:
Please Sign up or sign in to vote.
4.00/5 (6 votes)
15 May 20062 min read
How to implement data driven subscriptions in SQL RS 2000, Standard edition.


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|

    Image 1

  2. Now, set the schedule of the report to run once.

    Image 2

  3. Set the beginning date and the ending date to a date prior to today.

    Image 3

  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.

    Image 4


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Jason Selburg
Web Developer
United States United States
No Biography provided

Comments and Discussions

QuestionStored Procedure Pin
Member 1056193230-Jan-14 18:19
MemberMember 1056193230-Jan-14 18:19 
GeneralData-Driven Subscriptions Pin
Sreedevi Pidaparthi12-Dec-08 0:01
MemberSreedevi Pidaparthi12-Dec-08 0:01 
GeneralData Driven Subscriptions in SSRS 2005 Pin
Dilip Mysore Devaraj25-Oct-08 21:51
MemberDilip Mysore Devaraj25-Oct-08 21:51 
QuestionRe: Data Driven Subscriptions in SSRS 2005 Pin
momok_nz20-Jan-13 15:54
Membermomok_nz20-Jan-13 15:54 
AnswerRe: Data Driven Subscriptions in SSRS 2005 Pin
mecurioJ21-Jul-14 5:46
MembermecurioJ21-Jul-14 5:46 
GeneralRe: Data Driven Subscriptions in SSRS 2005 Pin
momok_nz21-Jul-14 10:18
Membermomok_nz21-Jul-14 10:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.