There are lots of ways to set up usable SQL Agent jobs to run SSRS subscriptions — I talked about this here — and also plenty of ways you can grab an SSRS report via external code, using either of the available APIs — I provide code for using .NET code and the REST API here, based on something I did as part of XMLRSDocs and for the SOAP API someplace else, I forget why.
But what if you want to be able to trigger subscriptions to reports in code, you can’t do it from an Agent job, but you only want to use SQL to do it?
The problem to solve
Some criteria for sending out reports and notifications just don’t lend themselves to schedules you can express in Agent. For a University, this includes reports that should occur after a term’s census date has been reached. The census date is the date on which we capture enrollment and other data for both analytics and compliance reporting. It’s loosely defined as “the 15th day of the month for two specific months (February and September)” but that definition is adjusted depending on holidays and weekends in any given year.
It’s definitely possible to derive the date in SQL, and I do. But invoking the report based on this logic is a little more complex — especially as, for our analytic server, I don’t have an Enterprise license, only Standard, so I can’t use data-driven subscriptions to run a check every day and only send on the correct day.
What not to do
One way to resolve this is to create a standard subscription, disable it, and then invoke it in code; executing an agent job in SQL is a simple thing to do. But this would leave me with a ton of guiddified, guidiotic Agent jobs, which makes me crazy. And this would be true even if I could use data-driven subscriptions.
Another problem with this approach is that, every time I needed to edit the subscription (for example, to add an email recipient to the list), the subscription would get re-enabled. Yes, I could edit the subscription only at the table level, rather than through the Report Manager. But this means that I’d be the only person who can maintain them, unless I create a custom UI and a bunch of code to pack the parameters into the two XML blobs (one for extension settings and one for parameters) that store them in the Subscriptions table. Considering that the parameter blob is different for each report (depending on its parameters, their types, etc) so I’d have to interrogate the report and then dynamically recreate the ReportManager UI for handling parameters… this is not really an acceptable use of my time.
So, here’s the current plan/walkthrough
My current approach doesn’t completely get rid of the guidiotic Agent job and schedule requirement, but it’s stable, no matter how often I need to edit the subscriptions. It doesn’t force me to edit the subscription on the table level. And it resolves all the different subscriptions I might want to treat in this way, into ONE guiddiful Agent job and schedule.
- Create an SSRS Shared Schedule to handle all these requirements. (So, one Agent guiddified job and one Agent guiddified schedule, but just one.) You can set it up as Run Once and even set the dates to the past, if you like.
- Create your report subscriptions — making sure to give them each a meaningful description, so you can query for that description later — and attach them all to the one shared schedule:
- If you check Agent, you will see that your dummy schedule-attached job using this syntax in a single job step :
exec [ReportServer].dbo.AddEvent @EventType='SharedSchedule', @EventData='03c86ef2-96a6-43a7-831e-6bfb8a4ca83f'
… this is because, using an EventType of SharedSchedule, the EventData parameter here is the Schedule ID.
So, if you were to programmatically invoke this line of code yourself, you’d be triggering all the subscriptions that are attached to this shared schedule. That’s definitely not what you want. - Instead, in your SQL code, when you’ve determined it’s the right time to send the report, simply query for your subscription and invoke it using the TimedSubscription EventType, just as you would if you’d set up the subscription with its own schedule. This works fine, and you use an EventData parameter value of the single Subscription ID. So your code to invoke the report, when the proper criteria are fulfilled, can look something like this:
declare @sub as uniqueidentifier select @sub = subs.SubscriptionID; from [ReportServer].dbo.Subscriptions subs where subs.Description = 'Post-Milestone Census Overview' ; if @sub is not null exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData=@sub ;
I suppose you could check to make sure you’re in the right place and that this subscription is still attached to the special dummy shared schedule, like this:
declare @sub as uniqueidentifier ; select @sub = subs.SubscriptionID from [ReportServer].dbo.ReportSchedule rsc join [ReportServer].dbo.Subscriptions subs on rsc.subscriptionid = subs.subscriptionid and subs.Description = 'Post-Milestone Census Overview' join [ReportServer].dbo.Schedule sched on rsc.ScheduleID = sched.ScheduleID and sched.name = 'Dummy - For all Code-Triggered Subscriptions' if @sub is not null exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData=@sub ;
… but I don’t see that it matters too much. If I wanted to extend this I’d probably create a table that stored Subscription descriptions against the conditions in which they should be invoked dynamically, anyway.
Extensions, alternatives?
You might be able to get away with no shared schedule, and not even one extra guid, and just delete the Agent job and attached Agent schedule once you’ve created things on the SSRS level. When I tried this, they kept getting re-created and annoying me, whenever a subscription edit was required. I’m pretty sure you could also just create the Subscription entirely programmatically and never bother with the ReportSchedule system at all, if you were willing to handle the parameters schema and/or never have users help maintain this.
I’m not “there”. In fact, this subscription-editing feature may actually be a place where the Report Manager does have an actual raison-d’être… or maybe I’m just getting lazy in my old age? Do I contradict myself? Very well, then.