Click here to Skip to main content
Click here to Skip to main content

Data Driven Subscriptions in SQL RS 2000 Standard

, 15 May 2006
Rate this:
Please Sign up or sign in to vote.
How to implement data driven subscriptions in SQL RS 2000, Standard edition.

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.

License

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

Share

About the Author

Jason Selburg
Web Developer
United States United States
No Biography provided

Comments and Discussions

 
QuestionStored Procedure PinmemberMember 1056193230-Jan-14 18:19 
GeneralData-Driven Subscriptions PinmemberSreedevi Pidaparthi12-Dec-08 0:01 
GeneralData Driven Subscriptions in SSRS 2005 PinmemberDilip Mysore Devaraj25-Oct-08 21:51 
QuestionRe: Data Driven Subscriptions in SSRS 2005 Pinmembermomok_nz20-Jan-13 15:54 
AnswerRe: Data Driven Subscriptions in SSRS 2005 PinmembermecurioJ21-Jul-14 5:46 
GeneralRe: Data Driven Subscriptions in SSRS 2005 Pinmembermomok_nz21-Jul-14 10:18 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140826.1 | Last Updated 15 May 2006
Article Copyright 2006 by Jason Selburg
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid