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.

Reporting Services Report – Changing Column Names, Changing Table Names and sql PIVOT

The other day I was creating some SSRS reports.  For each datafeed in an ETL process the rejected rows were being diverted into error tables – a single error table for each feed, eg error_feed1, error_feed2.  As each of the feeds were different, so too were the column names and metadata of the error tables.  To allow users to review and correct these records I needed to build Reporting Services reports on each of the tables.

Initially it looked like I would need a different report for each feed – when you bind a SSRS report object  to a dataset the column names of the dataset have to remain constant or the report will fail, hence one report, one error table.  As I was dealing with dozens of feeds, the prospect of dozens of very similar reports did not seem favourable.

All these reports would be almost identical, the only difference was the column names and the table names.  I was sure there must be an easier way.  I googled around and found several useful suggestions.  Generally they followed the idea of pivoting the columns into rows in the dataset and then using a SSRS matrix object.  EG:

error_feed1

Id	Col1	Col2	Col3	Col4
1	W	X	Y	Z

Would become:

ID	measure	value
1	Col1	W
1	Col2	X
1	Col3	Y
1	Col4	Z

Using a matrix you would put the ID column on the rows, the measure column on the cross tab section, and value in the data section of the matrix object.  Now it wouldn’t matter if the column names changed, if new columns were added or old ones removed from the source table.  The 3 columns outputted by the pivot query would remain and the matrix report will adapt accordingly. The pivot/unpivot command to do the above would look like this:

SELECT ID, measure, value
FROM( SELECT id, col1, col2, col3, col4, col5 FROM error_feed1) p
UNPIVOT( VALUE FOR measure IN (col1, col2, col3, col4, col5)) AS unpvt

The columns are now dynamic, which solves half the problem. But the FROM clause uses specific column and table names, meaning this metatdata needs to be known in advance and hardcoded into the SSRS dataset query.  Which brings us back to our original problem – we cannot hard code these values because they are constantly changing.

In order to get around this problem I decided to have the dataset be the result of a stored procedure.  I can then have greater flexibility in manipulating the data, so long as I return a result set to SSRS at the end, and always with the same column names returned.

The proc accepts one parameter  - the feed name, to be supplied by the user running the report using a standard SSRS drop down.

The proc itself makes use of the sysobjects and syscolumns system tables to get the full list of columns for any given table:

SELECT      c.name
FROM        sys.columns c
INNER JOIN  sys.objects o
      ON    c.object_id = o.object_id
WHERE       type = 'U'
      AND   o.name LIKE @TableNameORDER BY    c.column_id

name
------------
id
col1
col2
col3
col4

Once the table name has been supplied (as a parameter in the SSRS report), dynamic sql can be leveraged to query the system tables and use the results to build a string containing the required sql PIVOT command, with all the relevant column names for any given table.
 

Once the string variable is populated with the sql script it is then executed,  returning a result set of just 3 columns; the same 3 columns - ID, measure and value -  regardless of the table being queried.  The actual code code of the proc is below:

This result set is all that the SSRS queryset would ever see, and the column names would always be the same 3 named columns, regardless of the feed selected by the user.  Setting up the SSRS matrix object in the manner suggested above would then display the contents of the table as normal – effectively doing a PIVOT to counter the UNPIVOT done in the stored procedure.

We now only need  one single SSRS report to display data from any database table the user selects from the feed list in the parameter drop down -  much simpler than dozens of different reports, or dozens of datasets and playing with visibility settings etc.