{"id":18,"date":"2020-08-24T09:49:00","date_gmt":"2020-08-24T09:49:00","guid":{"rendered":"\/lisa\/post\/2020\/08\/24\/when-you-don-t-want-an-ssrs-subscription-at-all.aspx"},"modified":"2021-08-30T13:58:59","modified_gmt":"2021-08-30T20:58:59","slug":"when-you-dont-want-an-ssrs-subscription-at-all","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2020\/08\/24\/when-you-dont-want-an-ssrs-subscription-at-all\/","title":{"rendered":"When you don’t want an SSRS subscription AT ALL"},"content":{"rendered":"

There are lots of ways to set up usable SQL Agent jobs to run SSRS subscriptions — I talked about this here<\/a>\u00a0— 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<\/a>, based on something I did as part of XMLRSDocs<\/a>\u00a0and for the SOAP API\u00a0someplace else, I forget why.<\/p>\n

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?<\/p>\n

The problem to solve<\/h4>\n

Some criteria for sending out reports and notifications just don’t lend themselves to schedules you can express in Agent.\u00a0 For a University, this includes reports that should occur after a term’s census date<\/em> has been reached.\u00a0 The census date is the date on which we capture enrollment and other data for both analytics and compliance reporting.\u00a0 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.<\/p>\n

It’s definitely possible to derive the date in SQL, and I do.\u00a0 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<\/a>\u00a0to run a check every day and only send on the correct day.<\/p>\n

What not to do<\/h4>\n

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.\u00a0 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<\/em> use data-driven subscriptions.<\/p>\n

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.\u00a0 Yes, I could edit the subscription only at the table level, rather than through the Report Manager.\u00a0 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.\u00a0 Considering that the parameter blob is different for\u00a0each 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.<\/p>\n

So, here’s the current plan\/walkthrough<\/h4>\n

My current approach\u00a0doesn’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.\u00a0 And it resolves all the different subscriptions I might want to treat in this way, into ONE guiddiful Agent job and schedule.<\/p>\n

    \n
  1. Create an SSRS Shared Schedule<\/strong>\u00a0to handle all these requirements. (So, one Agent guiddified job and one Agent guiddified schedule, but just one.)\u00a0 You can set it up as Run Once and even set the dates to the past, if you like.
    \n\"\"<\/li>\n
  2. Create your report subscriptions — making sure to give them each a meaningful description, so you can query for that description later — and attach\u00a0them all to the one shared schedule:
    \n\"\"<\/li>\n
  3. If you check Agent, you will see that your dummy schedule-attached job using this syntax in a single job step :\n
    exec [ReportServer].dbo.AddEvent @EventType='SharedSchedule', \r\n@EventData='03c86ef2-96a6-43a7-831e-6bfb8a4ca83f'<\/pre>\n
    … this is because, using an EventType of SharedSchedule<\/span>,\u00a0 the EventData parameter here is the Schedule ID<\/span>.
    \nSo, if\u00a0you were to programmatically invoke this line of code yourself, you’d be triggering\u00a0all<\/em> the subscriptions that are attached to this shared schedule.\u00a0 That’s definitely not what you want.<\/li>\n
  4. 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.\u00a0 This works fine, and you use an EventData parameter value of the single Subscription ID.\u00a0 <\/strong>So your code to invoke the report, when the proper criteria are fulfilled, can look something like this:<\/span><\/span><\/span><\/span><\/span>\n
    declare @sub as uniqueidentifier \r\nselect @sub = subs.SubscriptionID;\r\nfrom [ReportServer].dbo.Subscriptions subs \r\nwhere subs.Description = 'Post-Milestone Census Overview' ;\r\nif @sub is not null \r\nexec [ReportServer].dbo.AddEvent \r\n@EventType='TimedSubscription', @EventData=@sub ;\r\n<\/pre>\n
    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:
    \n<\/span><\/span><\/span><\/span><\/span><\/p>\n
    declare @sub as uniqueidentifier ;\r\nselect @sub = subs.SubscriptionID \r\nfrom [ReportServer].dbo.ReportSchedule rsc\r\njoin [ReportServer].dbo.Subscriptions subs \r\non rsc.subscriptionid = subs.subscriptionid\r\nand subs.Description = 'Post-Milestone Census Overview' \r\njoin [ReportServer].dbo.Schedule sched on rsc.ScheduleID = sched.ScheduleID \r\nand sched.name = 'Dummy - For all Code-Triggered Subscriptions'\u00a0 \r\nif @sub is not null \r\n\u00a0 \u00a0 exec [ReportServer].dbo.AddEvent \r\n\u00a0 \u00a0 @EventType='TimedSubscription', @EventData=@sub ;<\/pre>\n
    … but I don’t see that it matters too much.\u00a0 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.<\/span><\/span><\/span><\/span><\/li>\n<\/ol>\n
    Extensions, alternatives?<\/span><\/span><\/span><\/span><\/h4>\n
    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.\u00a0 When I tried this, they kept getting re-created and annoying me, whenever a subscription edit was required.\u00a0<\/span><\/span><\/span><\/span>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.<\/p>\n
    I’m not “there”.\u00a0 In fact, this subscription-editing feature may actually be a place where the Report Manager does have an actual\u00a0raison-d’\u00eatre<\/em>… or maybe I’m just getting lazy in my old age?\u00a0\u00a0Do I contradict myself?\u00a0 Very well, then<\/a>.<\/span><\/span><\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
    There are lots of ways to set up usable SQL Agent jobs to run SSRS subscriptions — I talked about this here\u00a0— 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,Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6],"tags":[],"class_list":["post-18","post","type-post","status-publish","format-standard","hentry","category-reporting","category-sql-server"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/18","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=18"}],"version-history":[{"count":2,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/18\/revisions"}],"predecessor-version":[{"id":402,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/18\/revisions\/402"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=18"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=18"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=18"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}