SSAS: Acquiring Locks using XML/A from SSMS - Part 2

Mosha commented on my last post on this topic that there was another simpler way of doing the same thing from SSMS. And that is to execute the following commands from an MDX window.

First run ...

<BeginTransaction xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" />

and then...

<Lock xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
   <ID>496CEC1F-D66A-4C80-9E14-305D6CF41D86</ID>
   <Object>
     <DatabaseID>Adventure Works DW</DatabaseID>
   </Object>
   <Mode>CommitShared</Mode>
</Lock>

You will not get the nice syntax highlighting when using an MDX window, but it does still know how to run XML/A.

I probably should have figured this out myself as I pointed out last year that you can run XMLA commands from an MDX window and I mentioned in the last post that unlike XMLA windows which open a new session per command, MDX windows execute their commands on the same session. But at the time I was focusing more on showing some of the detail of the XML/A messages that are sent to SSAS than on finding the simplest approach.

But to take this even further you can enter the following into an MDX window and run the whole lot in one go.

BEGIN TRAN

GO 

<Lock xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
   <ID>496CEC1F-D66A-4C80-9E14-305D6CF41D86</ID>
   <Object>
     <DatabaseID>Adventure Works DW</DatabaseID>
   </Object>
   <Mode>CommitShared</Mode>
</Lock>

GO 

ROLLBACK TRAN

This exploits the GO statement, which is not really MDX, it is actually not even sent to the SSAS server, but SSMS treats it as a command delimiter. Obviously the above sample does not do much as it simply starts a transaction, takes a lock and the rolls back the transaction, but you could put in as many other commands or queries (separated by GO statements) as you liked after taking the lock.

Enjoy :)

Technorati Tags: ,

Print | posted on Thursday, January 24, 2008 4:07 AM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)