Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Limitation of Varchar 8000 in executing a dynamic MDX inside stored Procedure Expand / Collapse
Author
Message
Posted Friday, May 19, 2006 5:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 27, 2007 6:17 AM
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.

 

Post #281345
Posted Friday, May 19, 2006 12:07 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:06 AM
Points: 6,259, Visits: 2,030

Yes!

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

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

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

 




* Noel
Post #281504
Posted Friday, May 19, 2006 5:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:36 PM
Points: 35,347, Visits: 31,885

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #281554
Posted Monday, May 22, 2006 7:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 27, 2007 6:17 AM
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 ..

 

 

 

 

 

Post #281717
Posted Monday, May 22, 2006 9:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:36 PM
Points: 35,347, Visits: 31,885

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #281951
Posted Monday, May 22, 2006 10:29 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, December 8, 2008 8:42 AM
Points: 775, 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.

Post #281957
Posted Monday, May 22, 2006 11:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 27, 2007 6:17 AM
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 .

 

 

 

Post #281961
Posted Tuesday, May 23, 2006 4:00 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, December 8, 2008 8:42 AM
Points: 775, 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?

Post #282235
Posted Tuesday, May 23, 2006 8:29 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353

What is @mdxqry ?

Can you print it?

Post #282280
Posted Wednesday, December 4, 2013 5:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 4:50 AM
Points: 2, Visits: 77
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.
Post #1519564
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse