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.

SQL Server: Dynamically Check if SQL Agent Job is Running

It can be helpful to have a SQL script to determine if a SQL Agent job is currently running or not. In SSMS this check can be done using the tools and GUIs provided, but it may be necessary to embed this check in script.  For example you may have a series of scripts, some scheduled through jobs and some triggered by, say, a button click on an interface.  If you need to ensure that the button job does not run at the same time as the Agent Job, you'll need to dynamically check the agent job status. Here's how to do it.

First let's create a simple SQL Agent job with two steps:

In the above image I have created a new job called TestDelay, containing two steps, WAIT 1 and WAIT 2.  The first step simply waits for 20 seconds before completing successfully, while the second step waits for a further 5 seconds before doing the same.

The information relating to this job and it's execution is contained in the following tables in the msdb database:

dbo.sysjobs
dbo.sysjobsteps
dbo.sysjobactivity

From the first table we can find out the job_id, which is key for joining the 3 tables together:

SELECT job_id FROM msdb.dbo.sysjobs

WHERE name LIKE 'TestDelay'

Then from the second table we can find out "step" information:

SELECT step_id, step_name, subsystem

FROM msdb.dbo.sysjobsteps

WHERE job_id = (SELECT job_id FROM MSDB.dbo.sysjobs

                        WHERE name LIKE 'TestDelay')

step_id     step_name     subsystem
----------- ---------------------------
1           WAIT 1        TSQL
2           WAIT 2        TSQL

(2 row(s) affected)

Here we can see the two steps in our job and their step_ids. Using the third table we can run the following query for our job:

SELECT     start_execution_date,

           last_executed_step_id,

           last_executed_step_date

FROM       msdb.dbo.sysjobactivity a

INNER JOIN msdb.dbo.sysjobs j

ON   j.job_id = a.job_id

WHERE      j.name LIKE 'TestDelay'

start_execution_date    last_executed_step_id last_executed_step_date
----------------------- --------------------- -----------------------
2014-04-28 15:20:23.000 2                     2014-04-28 15:20:43.000

(1 row(s) affected)

We can see that on it's most recent execution, the job finished it's second and final step at 15:20:43. If the job were still running and had not completed even the first step yet,  then the last_executed_step_id and last_executed_step_date fields would be null for this row.
If, however, the first step has completed but the second has not, the two fields will be populated but the step_id will be 1, not 2.

So to know if our job is still running we really need to combine these three tables into a single query, checking whether the last_execution_step_id field is null, and if not, if it is at it's maximum value or not.

SELECT DISTINCT

            start_execution_date,

            last_executed_step_id,

            last_executed_step_date,

CASE  WHEN  last_executed_step_id = MAX(step_id) THEN 'Job Complete'    

      ELSE  'Job Still Running'

END         AS result

FROM        msdb.dbo.sysjobactivity a

INNER JOIN  msdb.dbo.sysjobsteps s

      ON    a.job_id=s.job_id

INNER JOIN  msdb.dbo.sysjobs j

      ON    j.job_id = a.job_id

WHERE       j.name LIKE 'TestDelay'

GROUP BY    start_execution_date,

            last_executed_step_id,

            last_executed_step_date

In the above query, the "result" field will say either "Job Still Running" or "Job Complete" depending on the logic stated above.  Alternatively the script could be rewritten in the form of a stored procedure that accepts a parameter containing the job name and returns a 1/0 depending on whether it is running or not.

It is worth examining the rest of the fields in these three tables as they can offer additional options for extracting information about the jobs set up on the server.