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.
use two ( several) variables the concatenate those in the exec statement like:
select @str1 = '... ' -- up to 8000
, @str2 = '...' -- up to 8000
Using Noeld's example, the EXEC statement might look like this...
Yeah... I know about xp_executeSQL...
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.
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:
Unclosed quotation mark before the character string
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?