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