Reporting Services Subscriptions through SQL Agent

Reporting Services subscriptions are useful (obviously) and can be configured adhoc or driven from config tables. The schedule that the subscription runs on can be run one of two ways.

1. At a time/date specified by you for that run - with the option of repeating

2. On a shared schedule

An option 3 might be

"when a particular job or activity has finished". 

If you could achieve this then you can reuse all the configuration you already have for the subscriptions. But how to do it? I found a hacky way that works. There may be other (and better) methods.

1. Create your subscription as normal, configuring your email receipients, delivery types etc.

2. Create an individual schedule that runs at an odd time (one you will recognise later) and make the end date for the schedule today.

3. Once you have created the schedule switch over to SQL Agent and try to identify the SQL Agent job that has been created by your subscription. You can either use the "created date" of the job or the "next run time". It will more than likely appear at the top of your job list and have an incomprehensible name containing characters and numbers.

4. Once you have located the job, open it up and it will contain a single step calling an sp with the name [AddEvent]. For example:

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='ABCDEF'

5. Now, open up the job you want to embed this in and create a new T_SQL step. Now just pop the line of code from Step 4 into the "Command" section

The reason for setting an expired EndDate in Step 2 is so that it does not actually run. Instead, the job will now be kicked off by your new job. In fact, it can be added to any job you like.