none
MDX query get truncated,when we have large value in where clause RRS feed

  • Question

  • HI ,

    I am writing MDX query ,where

    I declare a variable as varchar (max)

    write my query and have a linked server which bring the data from CUBE.

    SO when we have large data in filter(where clause) then the query gets truncated.

    where ( [Customer].[Customer Id].&[73] ,{[MeterID].[Meter Id].&[11013],[MeterID].[Meter Id].&[11024]..................................

    What is the limit of data I can store in varchar(max)?

    and how to fix the issue?

    Tuesday, May 21, 2019 5:14 AM

All replies

  • Hi Khushboo dubey,

    varchar [ ( n | max ) ] Variable-length string data. n defines the string length in bytes and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). 

    In addition, did you get any error information? You also could try to use subselect to see whether it work or not

    select [Measures].[Internet Sales Count] on 0 
    from (
    select ([Date].[Calendar Year].&[2012],{[Product].[Category].&[1],[Product].[Category].&[3]}) on 0 from [Analysis Services Tutorial])
    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 22, 2019 1:42 AM
  • This is not an MDX limitation. More likely it's an issue with however you are executing the statement through your linked server. If you are using the OPENQUERY function it has a limitation of 8000 characters. You could try the work around suggested here https://social.msdn.microsoft.com/Forums/sqlserver/en-US/031d3a2b-9095-4622-9ef5-7ac0f89e6f13/openquery-parameter-length-limitation-of-8000-characters?forum=transactsql or try asking this question in the T-SQL forum.

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

    Wednesday, May 22, 2019 3:57 AM
    Moderator