SSAS: Acquiring Locks using XML/A from SSMS

I put the following code sample together in response to this question on the Analysis Services forum.

If you read Books Online, you might think that running the following statement in SSMS would work :

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

But it will throw the following error:

Executing the query ...
Transaction errors: The request requires an active transaction.
Execution complete

Executing ...

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

... before hand does not help as time you run an XMLA statement from SSMS is that it executes in a new session, so the lock command gets executed on a different session to the one where the transaction was started. From looking at a profiler trace it appears that when you run a command from SSMS it opens a connection, executes the command and then closes the connection. (In contrast an MDX window appears to run all statements in the one session)

Now I don't actually think that there should be too many circumstances where you need to use an explicit lock. Individual commands will take their own locks anyway.  But what I did think would of interest, and is the reason I am posting this sample, is that it demonstrates how to execute a series of commands against a single session within a transaction from SSMS.

This involves constructing the entire SOAP envelope and I could not really find a lot of other samples around on how to do this. Using locking as an example provides a simple way of demonstrating this as we know that a lock can only be acquired inside a transaction.

Start off by running the following code to create a transaction :
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <Header>
    <BeginSession xmlns="urn:schemas-microsoft-com:xml-analysis"/>
  </Header>
  <Body>
    <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
      <Command>
        <BeginTransaction xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
</BeginTransaction> </Command> <Properties> <PropertyList> <LocaleIdentifier>1033</LocaleIdentifier> </PropertyList> </Properties> </Execute> </Body> </Envelope>

The xmla it returns will contain a SessionID, you will need to replace the SessionID in yellow below with the one returned from the first statement in order for the next statements to work. The GUID in green is one I generated by running "SELECT newid()" against my SQL Server, you could generate your own in the same way or use the Tools - Create GUID option in VS.Net.

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <Header>
    <Session xmlns="urn:schemas-microsoft-com:xml-analysis"
SessionId="C63C5C2B-7401-4127-8A03-D05F3D87ACBA" /> </Header> <Body> <Execute xmlns="urn:schemas-microsoft-com:xml-analysis"> <Command> <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> </Command> <Properties> <PropertyList> <LocaleIdentifier>1033</LocaleIdentifier> </PropertyList> </Properties> </Execute> </Body> </Envelope>

At this point you can execute any other statements you like on the same session. When you are finished finished you can call unlock ( I believe that committing or rollingback the transaction will also release the lock)

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <Header>
    <Session xmlns="urn:schemas-microsoft-com:xml-analysis" 
SessionId="C63C5C2B-7401-4127-8A03-D05F3D87ACBA" /> </Header> <Body> <Execute xmlns="urn:schemas-microsoft-com:xml-analysis"> <Command> <Unlock xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <ID>496CEC1F-D66A-4C80-9E14-305D6CF41D86</ID> </Unlock> </Command> <Properties> <PropertyList> <LocaleIdentifier>1033</LocaleIdentifier> </PropertyList> </Properties> </Execute> </Body> </Envelope>

And finally you need to either rollback or commit the transaction.

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
  <Header>
    <Session xmlns="urn:schemas-microsoft-com:xml-analysis"
SessionId="C63C5C2B-7401-4127-8A03-D05F3D87ACBA" /> </Header> <Body> <Execute xmlns="urn:schemas-microsoft-com:xml-analysis"> <Command> <RollbackTransaction xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" /> </Command> <Properties> <PropertyList> <LocaleIdentifier>1033</LocaleIdentifier> </PropertyList> </Properties> </Execute> </Body> </Envelope>

Print | posted on Sunday, January 13, 2008 11:28 PM

Comments on this post

# re: SSAS: Acquiring Locks using XML/A from SSMS

Requesting Gravatar...
Another, simpler, alternative would've been to run the

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

directly from the MDX window of SSMS in order to end up in the same session.
Left by Mosha on Jan 14, 2008 7:03 AM

# re: SSAS: Acquiring Locks using XML/A from SSMS

Requesting Gravatar...
Here are this and some other articles on Analysis Services Locks:

http://ssas-wiki.com/w/Articles#Locks

Left by Sam Kane on Mar 23, 2011 6:51 AM

# re: SSAS: Acquiring Locks using XML/A from SSMS

Requesting Gravatar...
Hi
How to write the above code in an SSIS package.
Left by Venkatesh K on Mar 18, 2013 10:51 PM

# re: SSAS: Acquiring Locks using XML/A from SSMS

Requesting Gravatar...
Hi Venkatesh,

You might be able to run it from an SSAS DDL task, but I'm not sure if you can read the session id. The other way would be to use a custom script task and do it all using AMO. But the more important question is way you would want to do this. The processing command is rich enough that I've never needed to manually control transactions. I've only ever used this technique to learn about the locking in SSAS.

Regards
Darren
Left by Darren Gosbell on Mar 19, 2013 6:35 AM

Your comment:

 (will show your gravatar)