Analysis Services

Killing Queries From Reporting Services

I guess anyone who has had to demo the new DMVs in Analysis Services 2008 has, like me, put together a few Reporting Services reports to show off what they can do. Now I don’t have anything much to add about the content of the DMVs and schema rowsets beyond what Darren and Vidas have already blogged, but I did discover an interesting trick while I was creating my reports that I thought would be good to share.

One of my example reports was displaying the results of the following DMV query:

select * from $system.discover_commands

What this does is display information on currently executing command or the last command that executed on every open session on the server (see here for more details). Pretty useful information in that if someone is running the query from hell on your AS server you’ll be able to see it here; what you’d really want to do though is act on this information and be able to kill the query. You can certainly get the same information and can kill the query if you’re using the Activity Viewer sample app that comes with the SQL Server samples (Jesse Orosz blogged about its features and shortcomings here); but can you do the same thing from within Reporting Services without writing any code? It turns out you can.

Here’s what my report looks like in BIDS 2008:


So far, so straightforward. Notice the column on the far right that contains the text "Kill This": this is static text and is a link to another report which takes one parameter, the SPID of the session that represents the current row. When you click on this link you jump to another report and it’s the act of running this report that kills the query on the session whose SPID you pass in. This second report looks like this in BIDS:

You’ll see that the design surface of the report contains a single text box with the message that your query has been killed successfully. The killing is done by the query in the sole dataset, and the query is generated by a Reporting Services expression that looks like this:

="<Cancel xmlns="""&quot;>
  <SPID>" + Parameters!SPID.Value.ToString() + "</SPID>

It’s an XMLA Cancel command with the SPID injected into it from the report parameter mentioned above. Two things to know to make it work:

  1. You have to execute it on an OLEDB-type connection, ie not Reporting Services’ "Analysis Services" connection
  2. The command doesn’t return any data and RS expects all its datasets to return at least one field. To work around this, you need to create a new dummy calculated field on the dataset that returns any value you like (you may see some error messages along the way but they can safely be ignored):

And bingo, you’ve got a basic activity monitor implemented in RS2008! I haven’t tested it, but you should be able to do exactly the same thing in RS2005 if you use the functionality in the XMLADiscover class Darren put into the Analysis Services Stored Procedure Project; the reason I like the approach I’ve just described more than this option is that whenever I’ve wanted to use some ASSP functionality in a production environment the customer has, for obvious reasons, not been comfortable uploading a dll onto their server.

9 thoughts on “Killing Queries From Reporting Services

  1. Chris,
    Great post!  I have done similair things in the past with RS2005.  In a past life we were using a report to monitor our Service Broker Queues and had the ability through SQL & RS to start and stop the queues.  It was very simple and easy to develop compared to writing some web code to do the same thing.

  2. Hi Chris,

    This is a really good SSRS report, I tried to develop the same kind of report at my kind and I stuck while writing the XMLA script to cancel the SPID in REport2 how to write the same, I mean in datasource like oledb how to specify a Cancel Script query for dataset. can u suggest more details. I want to build this report

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Can you tell me exactly where you’re having problems?

      1. Hi Chris,

        Iam facing problem when writing the below cancel XMLA query under dataset, it is giving error , how to provide the datasource for this as How to provide OLEDB datasource connection here and how to make sure that SSRS understands the below cancel query for SSAs and execute it. Again thanks for the reply

        ” + Parameters!SPID.Value.ToString() + ”

  3. Hi Chris,

    I tried to do run this report,this is not running as expected, can u please send the script of this SSRS report if u have.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.