Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
shagarwal
shagarwal
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1

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.


noeld
noeld
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6786 Visits: 2048

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
Jeff Moden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51429 Visits: 40305

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
shagarwal
shagarwal
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1

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
Jeff Moden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51429 Visits: 40305

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David McFarland
David McFarland
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 214

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
shagarwal
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1

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
David McFarland
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 214

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
Sergiy
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6360 Visits: 11526

What is @mdxqry ?

Can you print it?


kobust
kobust
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 81
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search