Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Killing Queries From Reporting Services

with 8 comments

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:

DMV1

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:

DMV2

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=""http://schemas.microsoft.com/analysisservices/2003/engine"&quot;>
  <SPID>" + Parameters!SPID.Value.ToString() + "</SPID>
</Cancel>"

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):

DMV3

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.

Written by Chris Webb

March 14, 2008 at 11:39 pm

Posted in Analysis Services

8 Responses

Subscribe to comments with RSS.

  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.

    Dan Meyers

    April 10, 2008 at 6:03 pm

  2. really useful!

    Peng

    November 16, 2008 at 11:16 pm

  3. Very usefull post! Thanks.

    Christo

    July 8, 2010 at 3:41 pm

  4. [...] easy way of killing a session from inside an SSRS report – I’m sure it’s possible, using this technique, but it won’t be elegant. To be honest, though, in most cases saving the trace to a table in SQL [...]

  5. 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

    soumya samprathi

    February 3, 2011 at 3:50 pm

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

      Chris Webb

      February 3, 2011 at 9:13 pm

      • 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() + ”

        soumya samprathi

        February 4, 2011 at 10:40 am

  6. 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.

    soumya samprathi

    February 10, 2011 at 12:11 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: