Subscriptions in Reporting Services are designed to run on a schedule and there isn?t a built-in feature to just run a subscription manually.
This is actually quite easy to do with a little bit of TSQL or .NET programming code. You can either use the SSRS web service API via code or command line script, or run the SQL Agent job using the sp_start_job system stored procedure. In either case, you will need the SubscriptionID GUID assigned to the SQL Server Agent job that was generated when the subscription was created. This is stored in the ReportServer Subscriptions table. Each SubscriptionID has a corresponding SQL Server Agent job named using the same GUID.
To find the subscription Agent jobs, open SQL Server Management Studio and connect to the relational instance hosting the report server content databases. Expand the SQL Server Agent jobs. Each job will have one step and will be scheduled to run at the time specified in Report Manager. Subscriptions have a TimedSubscription event type and reference the report?s ItemID key in the ReportServer database Catalog table. Hopefully, the can find this just be using the scheduled execution time. Copy the job name GUID to the clipboard. Do not modify this job or step but .
After you have the SubscriptionID/Job name, running the subscription is the easy part. In a T-SQL script, just execute this command substituting the GUID:
EXEC sp_start_job @job_name = ‘D4EF7E0D-7FFB-47A5-A87A-D63F2D242A06’
You can use an ADO.NET SqlCommand object to execute this code from web form or application.
The programmatic technique uses the SSRS web service API or the rs command line utility (which is actually a console wrapper around the web service interface). Using the command line option, the FireEvent method with the TimedSubscription argument will start the subscription using the Agent job name GUID:
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
This question comes up quite often. A report requires a user to be authenticated using their own network user name and password. Perhaps the data source performs row-level filtering for the user or the UserID global user object is used in a query or filter expression to return user-specific data. Whatever the case may be, how can you send scheduled subscriptions and filter the report for that user? This can be a challenge when scheduling a subscription for the report because a subscription requires that credentials be stored in the data source. This is not so much a limitation of Reporting Services as it is a logistical challenge. Why? ?because a subscription is designed to run unattended. The data source credentials must be stored so when the subscription fires in the middle of the night, the report and database query can run without the user logging in and sitting in front of their computer.
There are at least two possible solutions to this quandary . The first is to pass the UserID as a parameter and then filter the data in the query. The data source will still have to use a stored application credential but if user-specific filtering is the goal, this technique should meet the requirement. You can store each user?s UserID, along with any other report parameters and report execution settings (like the rendering format, etc.) in a table and then broadcast the report out to several users with a data-driven subscription.
The other approach is to use a scheduled subscription to email a link to the report, which will then run on-demand when the user clicks the emailed link. To do this:
- Deploy the report you want the user to run on-demand without stored credentials in the data source.
- Create a very simple, small report containing only a textbox to use as a link. Set a URL action on this textbox to open the other report, using the full path to the report on the server.
- Schedule a subscription for this small report and use the option to embed it into the email.
When the user receives the subscription email, they will just see a link, which as actually a report drill-through action that they will use to open the second report. Since that report doesn’t use stored credentials, it will use their own credentials to authenticate.