How can I get a list of Analysis Services databases on the server using XMLA?
Q: How can I get a list of Analysis Services databases on the server using XMLA?
A: You can use following XMLA discover command:
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DBSCHEMA_CATALOGS</RequestType>
<Restrictions />
<Properties />
</Discover>
To test this start Microsoft SQL Server Management Sudio, connect to Analysis Services server and from menu choose File->New->Analysis Services XMLA query. Paste above query and execute.
Result from my PC:
<return xmlns="urn:schemas-microsoft-com:xml-analysis">
<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">
<xsd:element name="root">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element name="row" type="row" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:simpleType name="uuid">
<xsd:restriction base="xsd:string">
<xsd:pattern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" />
</xsd:restriction>
</xsd:simpleType>
<xsd:complexType name="xmlDocument">
<xsd:sequence>
<xsd:any />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="row">
<xsd:sequence>
<xsd:element sql:field="CATALOG_NAME" name="CATALOG_NAME" type="xsd:string" minOccurs="0" />
<xsd:element sql:field="DESCRIPTION" name="DESCRIPTION" type="xsd:string" minOccurs="0" />
<xsd:element sql:field="ROLES" name="ROLES" type="xsd:string" minOccurs="0" />
<xsd:element sql:field="DATE_MODIFIED" name="DATE_MODIFIED" type="xsd:dateTime" minOccurs="0" />
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<row>
<CATALOG_NAME>Adventure Works DW</CATALOG_NAME>
<DESCRIPTION>A Unified Dimensional Model that encompasses the Adventure Works data warehouse.</DESCRIPTION>
<ROLES>*</ROLES>
<DATE_MODIFIED>2007-05-22T00:50:30</DATE_MODIFIED>
</row>
<row>
<CATALOG_NAME>Analysis Services Project1</CATALOG_NAME>
<DESCRIPTION />
<DATE_MODIFIED>2007-05-21T01:34:14</DATE_MODIFIED>
</row>
<row>
<CATALOG_NAME>VidasProject1</CATALOG_NAME>
<DESCRIPTION />
<DATE_MODIFIED>2007-05-17T02:45:29</DATE_MODIFIED>
</row>
</root>
</return>
This FAQ is based on Darren Gosbell blog entry.
Tags: faq, management