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? Managing multiple subscriptions can become a nightmare. But fear not, there is a solution available in the Standard edition of SQL Server that can help you manage your subscriptions efficiently.

In this article, we will explore a data-driven approach to managing SQL Server subscriptions using a stored procedure. This approach allows you to dynamically change the parameters and recipients of your reports without the need for creating multiple subscriptions.

The Data-Driven Subscription Approach

SQL Server Reporting Services (SSRS) stores subscription information in a table named “Subscriptions” and parameter information in a table named “Parameters”. By leveraging these tables, we can create a stored procedure that will look for predefined text in the subscription and replace it with the values we supply.

Although this approach may not be as visually appealing as the version available in SQL Server Enterprise edition, it gets the job done and is very useful. The stored procedure provided in this article allows for one parameter, but you can modify the code to suit your specific needs.

Implementing the Data-Driven Subscription

Here are the steps to implement the data-driven subscription approach:

1. Create the stored procedure using the code provided.
2. Create a new subscription for the report that you want to set up the data-driven subscription for. 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|
3. Set the schedule of the report to run once. Set the beginning date and ending date to a date prior to today to prevent the report from running unless called.
4. In SQL Server Management Studio, 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. Execute the subscription by calling the stored procedure and passing the necessary values.

Now you are ready to call your procedure from within your code and dynamically change the parameters and recipients of your reports. This approach eliminates the need for managing multiple subscriptions and provides a more efficient way to handle report distribution.

Conclusion

The data-driven subscription approach in SQL Server allows you to dynamically manage your report subscriptions without the need for creating multiple subscriptions. By leveraging the “Subscriptions” and “Parameters” tables, you can create a stored procedure that replaces predefined text with the values you supply. Although this approach may not be as visually appealing as the version available in SQL Server Enterprise edition, it is a practical and efficient solution for managing subscriptions in the Standard edition.

Feel free to modify the provided stored procedure to suit your specific needs. Any comments, suggestions, or improvements are welcome. Happy reporting!