Manually Execute SSRS 2012 Subscriptions

By far the easiest place to setup a SQL Server 2012 SSRS subscription for a report is through the Reporting Services Report Manager. A simple GUI guides users through the process of selecting a subscription type, where to save or send the output and on what schedule to execute. For example, you can specify the report to execute every morning at 9am and email the report as a pdf to a set of users, or to save the output file to a fileshare.

Now suppose you don’t want this report subscription to email or save the file on an automated schedule, but rather to be manually/actively triggered. Perhaps you want to test the subscription is working or you want it to be triggered by a third party application. There’s more than one way of doing this, and here I will cover two methods:

Method 1 – SQL Agent Job

When a subscription is created in the SSRS Reports Manager, a corresponding SQL agent job is also created on the SQL server. Unfortunately there is no way to control the name of the job and, once created, it cannot be modified without affecting the subscription. The name of the job will be an UID similar to the highlighted item below:

Right-click on this job and select “Start Job at step…” to run it. This will cause the subscription to execute, and you will see in the SQL Server Reporting Services Report Manager that the Last Run field for the subscription has been updated. This is the simplest approach and is particularly useful for testing the item:

Alternatively, a job request can be made using SQL code:

EXEC dbo.sp_start_job N'AAB7E0BD-8089-4330-A3D6-8B95ACD90132'

This will have exactly the same effect as right-clicking to execute the job.

The drawback to this approach is that SQL Agent job execution requests for a single job do not queue or run in parallel. If the job is already running then the subsequent job execution request will fail:

Request to run job AAB7E0BD-8089-4330-A3D6-8B95ACD90132 refused because the job already has a pending request

Therefore if multiple users are going to be issuing requests then this may not be the best approach.

Method 2 – Stored Procedure

If you open the SQL Agent Job used for the subscription and examine the step that has been defined, you will see that it is executing a stored procedure:

The AddEvent stored procedure is being called:

exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='a7fd8d5f-0af9-49ec-ad8e-b273d44f1bb0'

This stored procedure inserts a record in the ReportServer.dbo.Events table. The Events table is polled every few seconds by the server and any entries in this table will trigger the subscription to be executed. Note that the @EventData variable value is the same as the SQL Agent Job name.

This same SQL stored procedure can in fact be run manually against the SQL Server msdb database directly and will have exactly the same effect – it will trigger the subscription to execute. However, as we are no longer using the SQL Agent to call the procedure, we can actually run this code multiple times as quickly as we like. Each time it will insert a record into the events table, and every entry will trigger a new subscription execution, even if it is the same subscription. Effectively, subscription requests will be now be queued, and run sequentially.

If a subscription execution request needs to be made actively, or occur multiple times in a short space of time, and each request needs to be completed, then this method is a good approach to handling the scenario.

How to Execute SQL Script Asynchronously

SQL code generally runs sequentially.  One line of code must complete before the next line is executed.  We can redirect the path that is followed through the code using conditional logic or GOTO statements, but ultimately we cannot progress to the next step until the previous one has completed.

However, there are situations where we may want the code to continue to step 2 without waiting for a particularly time-consuming step 1 to complete.  If step 1 and step 2 do not impact each other then it might be beneficial to have the SQL execute asynchronously or concurrently, saving overall execution time.

For an example let's create a table to insert data into for step 1, and a stored procedure that will insert data into it:

CREATE TABLE [dbo].[InsertSomething](
       id                   INT IDENTITY(1,1) NOT NULL,
       Col1                 VARCHAR(32) NULL,
       InsertedDate         DATETIME NULL
)
GO

CREATE PROC DoSomething @Data VARCHAR(32) = ''
AS
INSERT INTO InsertSomething (Col1, InsertedDate) VALUES(@Data + ' - Start', GETDATE())
WAITFOR DELAY '00:00:10:00'
INSERT INTO InsertSomething (Col1, InsertedDate) VALUES(@Data + ' - End', GETDATE())
GO

The stored proc DoSomething will insert two rows into the table InsertSomething.  After the first row is inserted there will be a delay of ten seconds before the second row is inserted.  Therefore the time for the procedure to complete is approximately ten seconds, which in this example represents a long running piece of code.

EXEC DoSomething 'Test 1'

INSERT INTO InsertSomething (Col1, InsertedDate) VALUES('Test 2', GETDATE())
SELECT * FROM InsertSomething

Results

id          Col1                             InsertedDate

----------- -------------------------------- -----------------------

1           Test 1 - Start                   2014-12-30 12:38:40.023

2           Test 1 - End                     2014-12-30 12:38:50.030

3           Test 2                           2014-12-30 12:38:50.037

We can see from the timestamps that the "Test 2" record was inserted only after the stored procedure had completed inserting the Test 1 records. IE the sql script is executing sequentially, as expected.

In the above example we would prefer the "Test 2" insert statement not to have to wait for the stored procedure to complete. We would like the two sql commands to run concurrently.  To do this we need to create a second stored procedure to call the first via a new thread:

CREATE PROC [dbo].[AsyncInsert] @Data    VARCHAR(32)
AS

DECLARE @rc          INT
DECLARE @object      INT
DECLARE @osql_cmd    VARCHAR(1000)
DECLARE @ServerName  VARCHAR(256) = (SELECT @@SERVERNAME)

DECLARE @ExecStmt    VARCHAR(MAX) = '"EXEC DoSomething @Data='''+@Data + '''"'

EXEC @rc = sp_oacreate 'wscript.shell', @object OUT

SET @osql_cmd = 'osql -E -dMyDB -S' +@ServerName+' -Q'+ @ExecStmt

EXEC @rc=sp_oamethod @object, 'run', null, @osql_cmd

EXEC sp_oadestroy @object

GO  

Test the new procedure by running the following code:

EXEC [AsyncInsert] @Data = 'Test 1 Async'

WAITFOR DELAY '00:00:01:00'

INSERT INTO InsertSomething (Col1, InsertedDate) VALUES('Test 2 Async', GETDATE())

WAITFOR DELAY '00:00:10:00'

SELECT * FROM InsertSomething

Results
id          Col1                             InsertedDate
----------- -------------------------------- -----------------------
1           Test 1 - Start                   2014-12-30 12:38:40.023
2           Test 1 - End                     2014-12-30 12:38:50.030
3           Test 2                           2014-12-30 12:38:50.037
4           Test 1 Async - Start             2014-12-30 12:39:01.967
5           Test 2 Async                     2014-12-30 12:39:02.930
6           Test 1 Async - End               2014-12-30 12:39:11.987

In the results above we can see that for the Async tests the timestamps show that the Test 2 insert  occurred while the stored procedure (Test 1) was still running  - ie after the "Test 1 Async - Start" insert command completed but before the "Test 1 Async - End" insert was executed.

We have generated a new thread to execute the stored procedure that does not require us to wait for it to complete before continuing.  Therefore the SQL script is now effectively running asynchronously.