Monitoring Subscription Status (also calling RS SOAP methods from inside reports)
While posting on the Reporting Services forum, I came across a post by some folks looking to know how to automatically find out that a subscription had failed to run.
Right now, the way the RS tools are, there is no way to do this automatically without calling a SOAP method - ListSubscriptions. These folks were DBAs who wanted to monitor RS without needing to build an entire application to do so. The solution they were trying was to access the report server database directly to read values out its tables. They weren't impressed when I told them that MS does not support direct access to the report server database other than for getting data out of the execution log.
There are several ways to get around the lack of subscription monitoring feautres. First, you could write a set of scripts in VB.Net that leverage the RS.exe utility to output data to a location/database where you can analyze them. Second, you could write a report using the new XML data extension in SQL Server 2005 RS!
So to help these folks out, I've created a sample report that works on SQL Server 2005 RTM (english language server). It uses the new XML data extension which can be used to call SOAP/web service methods. Since RS exposes a SOAP API for listing subscriptions, we can use that to get the status and show the output.
[Update 8/17/2009]: The original reports I published have been further revised in the following post. The descriptions below of issues with monitoring subscriptions still applies. https://blogs.msdn.com/lukaszp/archive/2007/08/01/monitoring-subcription-status-new-reports.aspx [End Update]
Serveral notes:
1) Listing all subscriptions is hard from a security perspective. We made some decisions regarding how items are visible to users that make it difficult and we have not changed the design between RS 2000 and RS 2005. We're thinking of ways to make it better going forward.
1.1) The ListSubscriptions API lists the subscriptions for reports you have permission to see in the report server namespace. That means you need Browser role or higher on all reports or folders that have a distinct policy (do not inherit from their parent). You can avoid this by running the report as a local administrator on the computer the report server is running on because in RS we allow local administrators to see all items irrespective of policies (that way you don't ever lock yourself out by deleting all policies on the root!)
1.2) The ListSubscriptions API lists only subscriptions for Reports on which you have Manage Individual Subscriptions task or Manage Any Subscription task. Since you'll want to see all subscriptions that have failed, you'll need to have Manage Any Subscription task on all reports or folders that have a distinct policy (do not inherit from their parent). If you assign yourself the Content Manager role you should obtain sufficient permissions. Unfortunately, ensuring that you have sufficient permission on all items isn't trivial. This stems from a design decision we made early on in RS 2000 where we don't have an explicit 'administrator'.
2) The report uses string parsing to look for the words "; 0 errors" and "error" in the Status text. If you have a server language other than English, this won't work. Change the string to your language and it should work fine.
Ok, if you got through all of that, you should be able to upload the report, run it and see your subscriptions.
Lastly, a note on the XML data extension. The XML data extension that comes with SQL Server 2005 is really useful. You can use it to get data from just about any web service, and of particular interest/utility the RS web services. This means you can 'automate' just about anything by making the right SOAP call in the XML data extension in a report and then creating a subscription to the report in Reporting Services. So in the case above, if you need a daily report of which subscriptions failed, you can run the list subscriptions report using a subscription with a recurrence set to daily.
Best wishes and good luck,
-Lukasz
Comments
Anonymous
January 31, 2007
A customer came to the SQL Server Customer lab to investigate a problem with one of the feature I’m responsibleAnonymous
August 01, 2007
A while ago I wrote a blog post to explain how to monitor subscription status. You can read it here: