Limitation of Varchar 8000 in executing a dynamic MDX inside stored Procedure

  • I have a Stored Procedure in SQL 2000 which build a Long MDX query .Then i

    connect to OLAP

    and  execute the query .The dataset returned  is used in Report.

    When my dynamic query exceeds the varchar (8000) limit ,

    Error that comes  :

    ''Unquoted string in the Query'' .

    This error  comes only when my query exeeds the 8000 char limit. There

    is no syntactical error in the query.

    I have used following statement to connect to OLAP and execute my

    dynamic MDX

    exec('SELECT a.* FROM

    OpenRowset(''MSOLAP'',''DATASOURCE="RAPID-CHRISTUS"; Initial

    Catalog="MRS";'',' + @mdxqry + @mdxqry1+') as a')

    varchar @mdxqry  has a length of 8000 chars and

    varchar @mdxqry1 has a lenth of 5000 chars.

    Is there a way to execute a dynamic query in SQL 2000 whose length exeeds the varchar(8000) limit.

    Please help.

    TIA.

     

  • Yes!

    use two ( several) variables the concatenate those in the exec statement like:

    select @str1 =  '... ' -- up to 8000

            , @str2 = '...' -- up to 8000

     


    * Noel

  • Using Noeld's example, the EXEC statement might look like this...

    EXEC (@str1+@Str2)

    Yeah... I know about xp_executeSQL...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for the reply ,

    But unfortunately it still not working i tried following execute statement :

    exec ('SELECT a.* FROM OpenRowset(''MSOLAP'',''DATASOURCE="RAPID-CHRISTUS"; Initial Catalog="MRS";'',' + @mdxqry + ') as a')

    I get error that says :

    Unclosed quotation mark before the character string 'WITH  MEMBER [Measures].[PrincipalAmount] .......

     My MDX Query starts with the above statement.

    I know that exec statement can take 2 variables each having length of 8000 chars also . But i am not able to figure out why is this not working in this case ..Is it that i am connecting to OLAP Server first ..

     

     

     

     

     

  • The easy way to figure this out (find the problem) is to just type out the SELECT as you normally would...

    Then, everywhere you see a single quote, add another single quote right next to it.  Then wrap the whole thing in single quotes.

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • When testing dynamic SQL, I've found that replacing the EXEC with a PRINT statement usually makes the problem obvious. In fact, I almost always have two lines, one with EXEC @varname and the other with PRINT @varname, and comment out one or the other depending on whether I am running the script or just debugging it.

  • Thanks for ur replies,

    My syntax in MDX query is fine . I have tested the same query after Printing the statement in Analysis Services Application..It returns me a resultset.

    The Real Problem lies Connecting to OLAP from Sql server 2000 and executing a MDX query that is greater than 8000 chars .

    I am using following statement which gives an error only when the size of my query exceeds 8000 chars:

    exec ('SELECT a.* FROM OpenRowset(''MSOLAP'',''DATASOURCE="RAPID-CHRISTUS"; Initial Catalog="MRS";'',' + @mdxqry + ') as a')

    I get error that says :

    Unclosed quotation mark before the character string

    'WITH MEMBER [Measures].[PrincipalAmount] .......

    I feel theres some limitation in sql server 2000 .

     

     

     

  • In your original post, you used multiple variables as the string was over 8k. In your most recent post, you use a single variable to hold the MDX query. Did you perhaps forget to concatenate the two variables in your latest attempts?

  • What is @mdxqry ?

    Can you print it?

    _____________
    Code for TallyGenerator

  • I know that this is an old post, but it helped me out today. I never bothered to read up on execute().

    This will work. execute (@Query + @Query1 + @Query2), splitting the MDX script in three parts, each less than 8000 characters and declaring the variables as varchar(max).

    Now I can run MDX script of any length from my stored procedures. It used to be a pain.

Viewing 10 posts - 1 through 9 (of 9 total)

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