none
The server sent an unrecognizable response error when MDX query does not have X axis RRS feed

  • Question

  • Hi All,

    I am using following code to execute MDX query using ADOMD:

    var connection = new AdomdConnection(connectionString);
                var command = new AdomdCommand(mdxQuery, connection)
                {
                    CommandTimeout = 900
                 };
                try
                {
                    connection.ShowHiddenObjects = true;
                    connection.Open();
                    using (var adomdReader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                     int columnCount = adomdReader.FieldCount;
                     while (adomdReader.Read())
                     {
                          var drResult = dtResult.NewRow();
                            for (int index = 0; index < columnCount; index++)
                            {
                                    var v = adomdReader.GetString(index);
                            }
                        }
                }
                    }
                }


    When I have any mdx query which does not have X or Y axis in the query, ADOMD Reader.Read() thows Server  sent an unrecognizablr response exception.

    Here is a sample query:

    WITH 
      SET [x] AS 
        {} 
      SET [y] AS 
        SubSet
        (
          NonEmpty
          (
            {[Year].[Year].[All].Children}
           ,{[Measures].[main mesaure]}
          )
         ,0
         ,20000
        ) 
    SELECT 
      [x] ON 0
     ,[y] ON 1
    FROM [MY View]
    WHERE 
      {[Measures].[main mesaure]};

    Please provide suggestion how to retrive data when user have not selected any member on X or Y axis.

    Thank you

    

    Tuesday, April 9, 2013 7:43 AM

All replies

  • Hello,

    I don't understand, what your empty named set [x] is good for? If you want to receive an empty x axis, then query it as empty instead of the named set:

    WITH 
      SET [y] AS 
        SubSet
        (
          NonEmpty
          (
            {[Year].[Year].[All].Children}
           ,{[Measures].[main mesaure]}
          )
         ,0
         ,20000
        ) 
    SELECT 
      {} ON 0
     ,[y] ON 1
    FROM [MY View]
    WHERE 
      {[Measures].[main mesaure]};


    Olaf Helper

    Blog Xing

    Tuesday, April 9, 2013 7:54 AM
  • Hi Olaf Helper,

    Thank you quick reply. Well We have a UI tool which allows users to add members as rows and columns based on which we generate MDX query. Sometimes users does not add row or column, in such cases ADOMD reader's reader.Read() is throwing exception. I understand such query does not have significance but out of 1000 times user execute such query 3-4 times & i want to handle such scenarios.

    Thank you

    Tuesday, April 9, 2013 8:15 AM
  • Hi Webfriebd13,

    Thank you for your question. I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Regards,


    Elvis Long
    TechNet Community Support

    Monday, April 15, 2013 6:31 AM
    Moderator
  • I believe that this might be a limitation with the ADOMD DataReader in that you have to have at least 1 column.

    I think if you use a CellSet instead of a DataReader you do not have this limitation, but CellSets are a lot more complicated to code against because they are a much richer, multi-dimensional object.

    Another option might be that when you detect an empty set on the columns you insert  the measure on the columns instead of putting it in the WHERE clause.


    http://darren.gosbell.com - please mark correct answers

    Tuesday, April 16, 2013 4:24 AM
    Moderator
  • Hi Darren,

    Thank you for the reply. I was using CellSet to execute the queries and everything was working fine but I decided to move away from using CellSet as it has a large memory footprint. Is there any way to confirm this limitation of ADOMD DataReader class?

    Wednesday, May 15, 2013 4:21 AM
  • Hi Darren,

    Thank you for the reply. I was using CellSet to execute the queries and everything was working fine but I decided to move away from using CellSet as it has a large memory footprint. Is there any way to confirm this limitation of ADOMD DataReader class?

    So I'm not aware of anywhere that this is documented. To confirm this you could raise a support case with Microsoft .

    Or you could use a decompiler like Reflector or JustDecompile to open the Microsoft.AnalysisServices.Adomdclient.dll and look at the data reader implementation. That won't tell you if this limitation is by design or not, but it might tell you if there is any way of getting data reader to work with a query that has an empty set on the row axis.


    http://darren.gosbell.com - please mark correct answers

    Wednesday, May 15, 2013 5:54 AM
    Moderator
  • HI Darren,

    Thank you for quick reply. I will look into the Microsoft.analysisServices.AdomdClient.dll. 

    Wednesday, May 15, 2013 8:24 AM
  • Hi,

    As Darren suggested to look into the Read() function behavior when no axis is specified, you would need to open a support case.
    Please visit the below link to see the various paid support options that are available to better meet your needs.
    http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone 

    Also you could document this on the Microsoft Connect site (http://connect.microsoft.com/sqlserver).

    Thanks,
    Orsi
    Microsoft Online Community Support

    Sunday, June 2, 2013 10:29 AM
    Answerer