Post reply

DISCOVER_COMMAND_OBJECTS Not returning Rowset

  • December 18, 2015 at 8:45 am

    #150931

    I am trying to return data from the DMV "DISCOVER_COMMAND_OBJECTS". I have an MDX query that I test with that runs for over a minute, so I know that the query is running when I try and query the DMV. I have tried everything but still get no results. Anyone have any ideas. When I use XMLA or ADOMD.NET I am getting no results back.

  • December 18, 2015 at 8:50 am

    #1846809

    Can you post your query?

  • December 18, 2015 at 9:26 am

    #1846822

    First tried:

    select * from $system.DISCOVER_COMMAND_OBJECTS and received

    Executing the query ...

    Obtained object of type: Microsoft.AnalysisServices.AdomdClient.AdomdDataReader

    Execution complete

    So then tried XMLA with restriction on SPID:

    <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

    <RequestType>DISCOVER_COMMAND_OBJECTS</RequestType>

    <Restrictions>

    <RestrictionList>

    <SESSION_SPID>894</SESSION_SPID>

    </RestrictionList>

    </Restrictions>

    <Properties></Properties>

    </Discover>

    Results:

    <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" xmlns:msxmla="http://schemas.microsoft.com/analysisservices/2003/xmla">

    <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="SESSION_SPID" name="SESSION_SPID" type="xsd:int" minOccurs="0" />

    <xsd:element sql:field="SESSION_ID" name="SESSION_ID" type="xsd:string" minOccurs="0" />

    <xsd:element sql:field="SESSION_COMMAND_COUNT" name="SESSION_COMMAND_COUNT" type="xsd:int" minOccurs="0" />

    <xsd:element sql:field="OBJECT_PARENT_PATH" name="OBJECT_PARENT_PATH" type="xsd:string" minOccurs="0" />

    <xsd:element sql:field="OBJECT_ID" name="OBJECT_ID" type="xsd:string" minOccurs="0" />

    <xsd:element sql:field="OBJECT_VERSION" name="OBJECT_VERSION" type="xsd:int" minOccurs="0" />

    <xsd:element sql:field="OBJECT_DATA_VERSION" name="OBJECT_DATA_VERSION" type="xsd:int" minOccurs="0" />

    <xsd:element sql:field="OBJECT_CPU_TIME_MS" name="OBJECT_CPU_TIME_MS" type="xsd:long" minOccurs="0" />

    <xsd:element sql:field="OBJECT_READS" name="OBJECT_READS" type="xsd:long" minOccurs="0" />

    <xsd:element sql:field="OBJECT_READ_KB" name="OBJECT_READ_KB" type="xsd:long" minOccurs="0" />

    <xsd:element sql:field="OBJECT_WRITES" name="OBJECT_WRITES" type="xsd:long" minOccurs="0" />

    <xsd:element sql:field="OBJECT_WRITE_KB" name="OBJECT_WRITE_KB" type="xsd:long" minOccurs="0" />

    <xsd:element sql:field="OBJECT_ROWS_SCANNED" name="OBJECT_ROWS_SCANNED" type="xsd:long" minOccurs="0" />

    <xsd:element sql:field="OBJECT_ROWS_RETURNED" name="OBJECT_ROWS_RETURNED" type="xsd:long" minOccurs="0" />

    </xsd:sequence>

    </xsd:complexType>

    </xsd:schema>

    </root>

    </return>

    I then tried a .Net script using ADOMD.NET

    The XMLA and ADOMD.NET solutions I am assuming are returning empty rowsets even though I have active MDX queries executing, which I why I am stumped.

  • December 22, 2015 at 9:57 am

    #1847498

    According to this article (https://msdn.microsoft.com/en-us/library/hh230820(v=sql.120).aspx), the MDX query needs to have the following syntax to include restrictions:

    Select * from SYSTEMRESTRICTSCHEMA ($System.<schema rowset>, <restriction parameters and values>)

    I've tried it with the SPID restriction, but having some issues with the type conversion too.

    An interesting line in the documentation for this DMV: "The DISCOVER_COMMAND_OBJECTS schema rowset does not report activity through DMV queries." Not 100% clear on what this means, but it may be the reason for the empty rowset in most cases...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply