Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    2

    Unanswered: MDX Query above 8000 characters

    Hi,

    I have a MDX query which is of an aprox length of 10000 characters. I
    have to execute the query from within the stored procedure in sql. To
    run this query I use the openrowset method.

    If the length of my query is less than 8000 characters my query
    executes perfectly, but the moment it exceeds 8000 characters it stop
    working. Please suggest a solution for the same.

    Sample Code:

    declare @mdxqry varchar(8000)
    declare @SearchCond varchar(8000)

    set @SearchCond = @SearchCond + '
    [ProductsAccounts].CurrentMember.properties("AS Date") <= "' + @TDate
    + '" '

    set @mdxqry = '''WITH ' +
    'MEMBER [Measures].[Difference] as ''''[Measures].[Expected Interest
    Amount] - [Measures].[Adjusted Interest]'''' ' +
    'MEMBER [Measures].[Loan Closed within Report Period] as ' +
    '''''iif(cdate([ProductsAccounts].CurrentMember.properties("Closed
    Date")) < cdate("' + @ToDate + '"), "Yes", "No")''''' +
    'MEMBER [Measures].[ClosedBeforeLastInstallment] as
    ''''iif([Measures].[Loan Closed Before Last Instal]=1, "Yes", "No")''''
    ' +
    'SELECT ' +
    '{[Measures].[Expected Interest Amount], [Measures].[Adjusted
    Interest], [Measures].[Difference], ' +
    '[Measures].[Zero Interest Transactions],
    [Measures].[ClosedBeforeLastInstallment], ' +
    '[Measures].[Loan Closed within Report Period]} ON 0, '

    set @mdxqry = @mdxqry +
    '{Filter([ProductsAccounts].[Account Id].Members, (' + @SearchCond +
    '))} on 2, ' +
    @BranchFilter +
    'FROM InterestAnalysis'''

    set @mdxqry = 'SELECT a.* FROM
    OpenRowset(''MSOLAP'',''DATASOURCE="SERVERNAME"; Initial
    Catalog="DATABASENAME";'',' + @mdxqry + ') as a'

    exec(@mdxqry)

    I have already tried splitting my query into smalled chunks and
    executing it, but still I face the same problem.

    This is how I have Done it:

    declare @mdxqry1 varchar(8000)
    declare @mdxqry2 varchar(8000)
    declare @SearchCond varchar(8000)

    set @SearchCond = @SearchCond + '
    [ProductsAccounts].CurrentMember.properties("AS Date") <= "' + @TDate
    + '" '

    set @mdxqry1 = '''WITH ' +
    'MEMBER [Measures].[Difference] as ''''[Measures].[Expected Interest
    Amount] - [Measures].[Adjusted Interest]'''' ' +
    'MEMBER [Measures].[Loan Closed within Report Period] as ' +
    '''''iif(cdate([ProductsAccounts].CurrentMember.properties("Closed
    Date")) < cdate("' + @ToDate + '"), "Yes", "No")''''' +
    'MEMBER [Measures].[ClosedBeforeLastInstallment] as
    ''''iif([Measures].[Loan Closed Before Last Instal]=1, "Yes", "No")''''
    '

    set @mdxqry2 = 'SELECT ' +
    '{[Measures].[Expected Interest Amount], [Measures].[Adjusted
    Interest], [Measures].[Difference], ' +
    '[Measures].[Zero Interest Transactions],
    [Measures].[ClosedBeforeLastInstallment], ' +
    '[Measures].[Loan Closed within Report Period]} ON 0, '

    set @mdxqry2 = @mdxqry2 +
    '{Filter([ProductsAccounts].[Account Id].Members, (' + @SearchCond +
    '))} on 2, ' +
    @BranchFilter +
    'FROM InterestAnalysis'''

    set @mdxqry2 = 'SELECT a.* FROM
    OpenRowset(''MSOLAP'',''DATASOURCE="SERVERNAME"; Initial
    Catalog="DATABASENAME";'',' + @mdxqry + ') as a'

    exec(@mdxqry1 + @mdxqry2)

    Thanks in Advance

    Charu

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    varchar is limited to 8000 characters, but EXEC can take several varchar parameters concatenated together.

    Try breaking @mdxqry into several strings that you know will be below the 8k limit (@mdxqry1, @mdxqry2, ...@mdxqryN). Then call it like this:

    exec (@mdxqry1 + @mdxqry2 + .... @mdxqryN)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2006
    Posts
    2
    I have already tried breaking up the varchar into multiple variables but the same problem exists....Please see if there is any other solution....

    Thanks
    Charu

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by charugupta2608
    I have already tried breaking up the varchar into multiple variables but the same problem exists....
    How? Not according to the code you posted.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    May 2012
    Posts
    1
    Hi Charu-I am facing similar Issue in my procedure call mdx result from olap server have you find any work around for it?Please post if you have any.Thaks Ran

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you consult the OpenRowSet documentation, you'll see that the query is a single string. That imposes a hard limit of 8000 characters, I don't know of any way to work around it directly.

    Because of that limitation, executing this kind of MDX query directly from a stored procedure isn't a good fit. I would recommend re-engineering the process if you can, or at least using a SQL Agent Job to run a SSIS Package which can run the MDX query for you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •