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

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.