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

Question

Post reply

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

shagarwal

SSC-Addicted

Points: 461
More actions
May 19, 2006 at 5:39 am

#168486

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.

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

Yes!

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

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

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

* Noel

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

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)

shagarwal SSC-Addicted Points: 461 More actions · Answer 3

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 ..

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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)

David McFarland SSChampion Points: 11815 More actions · Answer 5

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.

shagarwal SSC-Addicted Points: 461 More actions · Answer 6

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 .

David McFarland SSChampion Points: 11815 More actions · Answer 7

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?

Sergiy SSC Guru Points: 110208 More actions · Answer 8

What is @mdxqry ?

Can you print it?

_____________
Code for TallyGenerator

kobust SSC Rookie Points: 30 More actions · Answer 9

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.