Skip to main content

SSRS 2008 - Getting the schedule of a subscription on a report

After wasting a few hours, I've decided it's not worth it to proceed any further. If you want to know the schedule (or just the next-run-time) of a subscription, just go to the SSRS web site (the web UI). It's too much of a pain to use the ReportingService2005 interface.

The simple back-story to this is that I've made an app that handles uploading (create/update) all of our reports (.RDL files) from the local computer to an SSRS instance. Doing this through the web UI is incredibly tedious, error-prone, and my Carpal Tunnel flares up after a few reports. So now that I'm done, I decided that it would be nice to see information about all of the various subscriptions that people have defined for the various reports.

It's not hard to add this in. You just call ListSubscriptions(null, null) after logging in with an admin account. But the Subscription object has no information about the schedule it runs against! Well, actually it can tell you a few things via the LastExecuted, Status, & EventType properties, but I'm really liking the information in a Schedule object.

So maybe if you start with Schedule objects and find a way back to a Subscription on a Report? Nope. ListSchedules() and ListScheduledReport() only work on "shared" schedules; if you define a schedule when you're defining a subscription (via the web UI), your schedule isn't returned by those 2 methods. And it wouldn't matter anyhow, because the Schedule object does not have anything linking it to a Subscription or a Report.

There is only one possibility left that I'm aware of: get a ScheduleDefinition object in one of two ways:

  1. GetSubscriptionProperties() - This is a very cumbersome method, and the only thing you get from it that a Subscription object doesn't already have is in the "MatchData" parameter. It returns an Xml-serialized ScheduleDefinition object. Deserialize it, and you can then do the additional work of casting the Item property to the appropriate type and re-calculating the data that a Schedule object would have already had available to you. Oh boy.
  2. GetExecutionOptions() - This should be better only because it should return you an object (rather than having to deserialize some Xml). But what happens if multiple users have defined multiple subscriptions on the report? It doesn't return a list, so either it fails or only returns one of the schedules (definition or reference).

 It seems the more I work with SSRS, the less I like its architecture. I considered it an "epic-fail" back when I had to deal with implementing a custom security extension (e.g. RSReportServer.config's "Dsn" and "URLReservations" tags---SSL support is broken because the UI doesn't use relative paths and hard-coded "http" is all over the place---the list goes on and on). Now they're just taunting me.
Labels:

Comments

Post a Comment

Popular posts from this blog

SqlBulkCopy and the "colid" error

I thought there was a page explaining this somewhere out there on the Internet, but I can't find it anymore. So here's what I re-discovered. When you try to insert the rows from a DataTable and the data in one of the columns of one of the rows is too big to fit into the destination column in the database, you get a SqlException with this error message: "Received an invalid column length from the bcp client for colid N." (Where "N" is a number.) It doesn't tell you which row, and it's a pain to figure out what column to look at. To determine what column it is referring to, you first need to get a listing of all columns in the table, listed in the order as they have been defined in the database. Next, you remove any columns in the list that are not represented in SqlBulkCopy.ColumnMappings (the order of the column mappings is irrelevant). The list that remains is what "colid" is referring to, with the first column corresponding to colid &
Post a Comment
Read more

Live Migration between domains

For those of you like me who aren't experts at all things Active Directory (AD) and Hyper-V Live Migration (LM) permissions, it can be enough of a pain to LM a Virtual Machine (VM) between domains that you simply decide to take the VMs offline to affect the move. See, I only tolerate AD because it's required for LM'ing VMs; there isn't a choice. (It's also required for Windows Clusters, but that's a different topic.) But I figured it out. My back-story is that we setup a cluster using Windows 2012 r1 as the AD Domain Controller (DC) and Hyper-V Server 2012 r1 for the VM hosts. Then we decided we wanted to use r2 for the AD DC and Hyper-V hosts. Upgrading Hyper-V was easy. But I found that there's some unresolved Microsoft bug with Windows Clustering when upgrading the AD DC from Windows 2012 r1 to Windows 2012 r2--- clustering simply doesn't work correctly anymore . So we gave up and created a from-scratch Windows 2012 r2 AD DC then made a new cluster
Post a Comment
Read more

Hyper-V and reserving RAM for the host/root/parent partition

After a long process, I finally have a real-world calculation for determining how much RAM to reserve for a Hyper-V host. The question/answer about it is here. But the summary is that Hyper-V loses RAM to the Nonpaged pool (and all of it is "untagged") in addition to the "standard" stuff that Microsoft has documented. Be aware that I write MB/GB here, when I actually mean MiB/GiB. I feel it will be more intuitive to see the notation that Windows (incorrectly) uses. Host Overhead 300 MB for the Hypervisor services 512 MB for the Host OS (This is a recommended amount; you have some wiggle-room with this.) [The amount of physical RAM available to the host OS] multiplied by 0.0425 (result in GB ) for the Nonpaged pool (Which means multiply that by 1024 to convert to "MB") Per-VM Overhead 24 MB for the VM 8 MB for each 1 GB of RAM allocated to the VM. Examples 12 GB RAM, 1 VM @2 GB, 1 VM @4 GB Host: 812 + (0.0425 * 12 * 1024) = 1,334.24 MB
Post a Comment
Read more