This project has moved and is read-only. For the latest updates, please go here.

discoverXmlMetaData not working on SQL Server 2012 SP2 CU 8

Topics: Developer Forum, User Forum
Nov 2, 2015 at 1:18 PM
Hi

I have been using the select discoverXmlMetaData for many years now. But when we recently applied the CU8 patch for SQL Server 2012 SP2 things stopped working.

When executing the procedure CALL ASSP.discoverXmlMetaData("\Database\Cubes\Cube\") I don't get any results. (no errors just Executing the query ...Execution complete) On our other server where we have not applied CU8 we get results as before.

Anyone else having issues with 2012 SP2 CU8 and ASSP version 1.3.7

/Johan
Nov 2, 2015 at 2:00 PM
I have done some more testing. I can't even get any result for this query:
CALL ASSP.AssemblyVersion()

But things like this still works.
with member [Measures].[LastProcessed] as ASSP.GetMeasureGroupLastProcessedDate("Fact Test")
select [Measures].[LastProcessed] on 0
from [TEST CUBE]


/Johan
Nov 4, 2015 at 4:10 AM
So both of those function calls that are not working return rowsets (.Net DataTables) so there may be some bug that's been introduced there. If you run the following in an XMLA window do you see a response coming back?
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
    <Body>
        <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
            <Command>
                <Statement>
                    Call ASSP.AssemblyVersion()
                </Statement>
            </Command>

            <Properties>
                <PropertyList>
                    <Catalog>AdventureWorks</Catalog>

                    <!-- 
                <Format>Tabular</Format> 
                <Content>Data</Content>

-->
                </PropertyList>
            </Properties>

        </Execute>
    </Body>
</Envelope>
You should be seeing something like the following:
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <ExecuteResponse xmlns="urn:schemas-microsoft-com:xml-analysis">
      <return>
        <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="Version" name="Version" type="xsd:string" minOccurs="0" />
                <xsd:element sql:field="Major" name="Major" type="xsd:int" minOccurs="0" />
                <xsd:element sql:field="Minor" name="Minor" type="xsd:int" minOccurs="0" />
                <xsd:element sql:field="Build" name="Build" type="xsd:int" minOccurs="0" />
                <xsd:element sql:field="Revision" name="Revision" type="xsd:int" minOccurs="0" />
              </xsd:sequence>
            </xsd:complexType>
          </xsd:schema>
          <row>
            <Version>1.3.6.0</Version>
            <Major>1</Major>
            <Minor>3</Minor>
            <Build>6</Build>
            <Revision>0</Revision>
          </row>
        </root>
      </return>
    </ExecuteResponse>
  </soap:Body>
</soap:Envelope>
Nov 5, 2015 at 2:21 PM
Edited Nov 5, 2015 at 2:26 PM
Hi,

same here with SQL Server 2014 SP1 CU3. It was fine with SQL Server 2014 SP1 CU2.

Neither
CALL ASSP.AssemblyVersion()
nor
CALL assp.discoverxmlmetadatafull(...)
doesn't work.

When running ASSP.DISCOVERXMLMETADATAFULL my response is:
Executing the query ...
Execution complete
with no results.

My response with last XMLA is:
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <ExecuteResponse xmlns="urn:schemas-microsoft-com:xml-analysis">
      <return>
        <root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" 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" />
      </return>
    </ExecuteResponse>
  </soap:Body>
</soap:Envelope>
Thanks,
Krzysztof
Nov 5, 2015 at 10:28 PM
I just checked with a contact on the product team. He had a look at the changes for SQL 2012 SP2 CU8 and said that he could not see anything that should have caused this. Are either of you in a position to be able to raise a support case about this issue?
Nov 6, 2015 at 1:13 PM
Yes, I am raising it now.
Nov 14, 2015 at 6:35 PM
Hi,

I can confirm that I am facing the same issue. Can you please let me know if this being looked at?

I face this issue only on Developer Edition and not on the Enterprise Edition. (Both 2012 CU8 patched version)

Thanks,
Nov 15, 2015 at 11:08 PM
So all the information we have is in this thread.

The situation is.
  1. Code works pre SQL 2012 SP2 CU8 / SQL 2014 SP1 CU3
  2. You install either of those updates without changing anything in ASSP
  3. Any stored procs which return a rowset now return nothing, there are no errors thrown and there have been no code changes in ASSP
So this is pretty clearly a Microsoft issue. Apparently @kzajaczkowski was raising this with Microsoft. If you want this addressed urgently you might consider doing the same. Can you double check that your build numbers in SSAS are identical between Enterprise and Developer? I've upgraded an Enterprise VM to SQL 2012 SP2 CU8 (so SSAS reports 11.0.5634.1 in SSMS) and it is exhibiting the same behaviour. I've single stepped through the code in a debugger and it is still working and producing the expected result without any apparent error, that results is just not getting transmitted back to the client.
Nov 16, 2015 at 12:25 PM
Clarification of my testing. We went from SQL Server 2012 SP2 CU2 -> SQL Server 2012 SP2 CU8. So I really don't know in which CU this stopped working. It could by any between CU3 and CU8.
Nov 17, 2015 at 9:54 AM
I raised it with Microsoft on 6th November, but still no reply.
In SQL Server 2014 it works on SQL 2014 SP1 CU2 and doesn't on SP1 CU3.
Nov 17, 2015 at 10:43 AM
Can you post the support case number? We can follow up with some contacts.
Nov 17, 2015 at 10:46 AM
Edited Jan 13, 2016 at 9:33 AM
Of course, it is: 115110613347761
Thanks!

UPDATE (2016-01-13)
Answer from Microsoft Support:

Observations:

Please refer article: https://technet.microsoft.com/en-us/library/ms175314(v=sql.110).aspx
In this article, the snippet clearly mentions:
<snippet>
The only supported type returned from stored procedure in a Call statement is a rowset. The serialization for a rowset is defined by XML for Analysis. If a stored procedure in a Call statement returns any other type, it is ignored and not returned in XML to the calling application. For more information about XML for Analysis rowsets, see, XML for Analysis Schema Rowsets.

If a stored procedure returns a .NET rowset, Analysis Services converts the result on the server to an XML for Analysis rowset. The XML for Analysis rowset is always returned by a stored procedure in the Call function. If a dataset contains features that cannot be expressed in the XML for Analysis rowset, a failure results.
</snippet>

As vishwa mentioned in the email, we have to alter the code in the codeplex to return a rowset instead of a datatable.

Action plan:

1) The code at the above codeplex location will have to be changed to return rowset rather than DataTables.

2) I can understand that you are of the opinion that this works fine in CU2 but fails in other CU version but it would be difficult to debug into an assembly that is hosted on codeplex the reason being the product (SSAS) is behaving as documented.

3) Therefore , it is advisable to contact the developer of the ASSP application and understand the supportability of the application on different versions.
Jan 13, 2016 at 10:38 AM
That's a bit of a disappointing response. It does not sound like they've put much effort in. There are a number of other stored procs including ListFunctions() which have been working fine since at least SSAS 2008, possibly even SSAS 2005. The full source code is available on codeplex so it would be a trivial matter for them to download and debug it if they so desired.

I'd be happy to look at modifying the code. All we need is a C# sample of how to return a Rowset as it is my understanding that returning a .net datatable was the correct way of doing this as this technique has been working for the last 6-7 years. I've traced the existing code on SQL 2012 SP2 prior to CU8 and it does indeed return a rowset, after CU8 is installed it no longer works.

I'll forward this on to my contacts in the dev team and see if they can shed any more light on this situation.
Feb 5, 2016 at 5:46 AM
@kzajaczkowski - have you had any further feedback from the support guys? Do you know if they even tried running these stored procs on a version of SSAS prior to ones that cause the issue? (they could run the ASSP.ListFunctions() procedure as a test which does not even require a specific database)
Feb 11, 2016 at 8:06 PM
I've spoke to a couple of the developers who are now looking into this, unfortunately they have not been able to replicate the issue. I've sent through screenshots and a couple of debug dumps to them so hopefully we'll get some movement on this issue soon.
Feb 13, 2016 at 3:58 AM
Talking to development team it looks like a problem isolated to the way CU8 was built. I have not had a chance to test this myself yet, but we believe that this was corrected in SQL 2012 SP2 CU9. So try installing that CU should correct this issue.

It looks like SQL 2014 SP1 CU3 & CU4 may also be affected by this, the fix for 2014 should be coming in a subsequent CU (I don't have any more details than that at this time - I'll post back here when we know for sure which CU fixes this for 2014)
Feb 25, 2016 at 4:19 AM
OK, so this has been confirmed as a Microsoft bug.

If you are running SQL 2012 SP2 CU8 -> Upgrade to SQL 2012 SP2 CU9 to fix this

If you are running SQL 2014 SP1 CU3 or CU4 -> Upgrade to SQL 2014 SP1 CU5 to fix this
Marked as answer by dgosbell on 2/24/2016 at 8:19 PM
Mar 3, 2016 at 10:54 AM
@dgosbell, thank you very much for following it up with Microsoft! We'll check out CU5 then.
Mar 3, 2016 at 11:40 AM
No worries, thanks for logging the case. It gave us something concrete to get the dev team to look at. Turns out that the internal build for those CU's works fine (which is why the support team had trouble reproducing the issue). The issue was in the installer for the CU.
Mar 30, 2016 at 8:51 AM
Edited Mar 30, 2016 at 9:18 AM
We tried to update to CU5, but the queries still didn't work. Are there any other changes necessary?
Mar 30, 2016 at 12:43 PM
So if you are on SQL 2014 SP1 then yes, installing CU5 fixes the issue reported in this discussion.

What query are you trying to run? Have you tried running "CALL ASSP.ListFunctions()" or "CALL ASSP.AssemblyVersion()"?

What is the full version number do you see against your SSAS instance when you connect using SSMS?