DISCOVER_COMMAND_OBJECTS Not returning Rowset

Question

Post reply

DISCOVER_COMMAND_OBJECTS Not returning Rowset

stan.geiger

Right there with Babe

Points: 727
More actions
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.

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

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

Martin Schoombee SSCoach Points: 19126 More actions · Answer 1

Martin Schoombee

SSCoach

Points: 19126

December 18, 2015 at 8:50 am

#1846809

Can you post your query?

stan.geiger Right there with Babe Points: 727 More actions · Answer 2

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:

<RequestType>DISCOVER_COMMAND_OBJECTS</RequestType>

<SESSION_SPID>894</SESSION_SPID>

</RestrictionList>

</Restrictions>

</Discover>

Results:

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

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

Martin Schoombee SSCoach Points: 19126 More actions · Answer 3

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