Executing SQL Server Reporting Services Subscriptions

Author: Louis Young, SQL Developer

Not too long ago, a discussion was had with a client around the implementation of our SQL Server Reporting Services project. There are various dependencies on external processes to complete before the report executes. The client manages these dependencies in CA7, so the obvious solution is to run the reports from CA7 as well. After some analysis, it was established that if the SSRS reports were run from the command line, then CA7 will be able to run them.

This post will discuss the various steps involved in executing SSRS reports from the command line.

The first requirement is to deploy a couple of reports to the SSRS server. For the purpose of this exercise, please consult a few sample reports.

Now create a subscription to one of the reports.

Edit the report’s schedule to run only once.

Choose the preferred method of delivery and create the subscription.

Edit the subscription again, paying close attention to the URL in the browser’s address bar. It should look something like this:

http://lap07820/Reports_SSRS/manage/catalogitem/editsubscription/Visual%20Reports/Dashboard?id=598bd389-da19-4737-b685-e6705424139a.

The red section above is a GUID created by SSRS for each subscription respectively. We will use this GUID to execute the subscription.

  • First, let’s see how this subscription from a stored procedure is executed;
  • Open SSMS and connect to the SSRS database (ReportServer by default).
  • Using the GUID from the URL above, execute the following stored procedure.

EXEC

dbo.AddEvent

@EventType = ‘TimedSubscription’,

@EventData = ‘598bd389-da19-4737-b685-e6705424139a’;

Confirm that the subscription ran and generated the report. If the subscription ran successfully, that is half the battle won. Now all that is needed is to do the same thing from the Windows command line:

SQL Server ships with a command line utility called sqlcmd. To see a list of available options, open the command prompt and type the following:

sqlcmd /?

To run the report subscription again from the command line, execute the following:

sqlcmd -d ReportServer -Q “EXEC dbo.AddEvent =’TimedSubscription’, = ‘598bd389-da19-4737-b685-e6705424139a’;”

Confirm that the subscription ran a second time.

This may seem trivial, but when the client and peers alike proclaimed: “Impossible” – EOH made it possible!

Leave a Reply

Your email address will not be published. Required fields are marked *