how to lock a database using XMLA
I want to lock a database using XMLA as follows:
<Lock xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ID>
?
</ID>
<Object>
<DatabaseID>test1</DatabaseID>
</Object>
<Mode>CommitShared</Mode>
</Lock>But I don't known how can I get the lock's UUID?
Answers
There should not be many circumstances where you need to use an explicit lock. A lock can only be created within a transaction and all your commands need to be executed on the same session. So the full code to aquire a lock from SSMS looks something like the following:
Run this code to create a transaction
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Header>
<BeginSession xmlns="urn
chemas-microsoft-com:xml-analysis"/></Header>
<Body>
<Execute xmlns="urn
chemas-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 Red 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
chemas-microsoft-com:xml-analysis" SessionId="C63C5C2B-7401-4127-8A03-D05F3D87ACBA" />
</Header>
<Body>
<Execute xmlns="urn
chemas-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 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
chemas-microsoft-com:xml-analysis"SessionId="C63C5C2B-7401-4127-8A03-D05F3D87ACBA" />
</Header>
<Body>
<Execute xmlns="urn
chemas-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
chemas-microsoft-com:xml-analysis"SessionId="C63C5C2B-7401-4127-8A03-D05F3D87ACBA" />
</Header>
<Body>
<Execute xmlns="urn
chemas-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>
All Replies
It appears to simply require a valid GUID, I generated one using the following T-SQL query against the relational engine and it worked fine.
SELECT newid()
However, can I ask why you need to take an explicit lock? SSAS takes out implied locks for single statements. With a CommitShared lock are you trying to ensure that nothing can change in the database in between two (or more) queries?
I just want to try this command.I create a new XMLA query for the database named test1, the query as follows:
<Lock xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ID>
?
</ID>
<Object>
<DatabaseID>test1</DatabaseID>
</Object>
<Mode>CommitShared</Mode>
</Lock>In this scenario, how to get ID,or may be my query is wrong. Thanks!
There should not be many circumstances where you need to use an explicit lock. A lock can only be created within a transaction and all your commands need to be executed on the same session. So the full code to aquire a lock from SSMS looks something like the following:
Run this code to create a transaction
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Header>
<BeginSession xmlns="urn
chemas-microsoft-com:xml-analysis"/></Header>
<Body>
<Execute xmlns="urn
chemas-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 Red 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
chemas-microsoft-com:xml-analysis" SessionId="C63C5C2B-7401-4127-8A03-D05F3D87ACBA" />
</Header>
<Body>
<Execute xmlns="urn
chemas-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 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
chemas-microsoft-com:xml-analysis"SessionId="C63C5C2B-7401-4127-8A03-D05F3D87ACBA" />
</Header>
<Body>
<Execute xmlns="urn
chemas-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
chemas-microsoft-com:xml-analysis"SessionId="C63C5C2B-7401-4127-8A03-D05F3D87ACBA" />
</Header>
<Body>
<Execute xmlns="urn
chemas-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>
It works.Thank you very much for your time and effort.
