Probem in executing a long Dynamic MDX from SQL server

[agarwalshuchi]

Hi ,
What i exacly want to do is

1. Connect to OLAP server from my Sql server using following query
string

'SELECT a.* FROM
OpenRowset(''MSOLAP'',''DATASOURCE="RAPID-CHRISTUS"; Initial
Catalog="MRS";

2. I want to execute my dynamically created MDX query . This query can
be greater than 8000 varchar limit.
When my query length exceeds 8000 length i break it up into 2 parts
..Here I have broken my query into 2 parts

@mdx1 and @mdx2

Now i execute the entire statement as

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

Still error comes that :
Unclosed quotation mark before the character string 'WITH MEMBER ..

('With member' is the starting statement of my MDX query)

Is there any other way to connect to OLAP server and execute an MDX
statement with a Length greater than 8000 chars

TIA

[Erland Sommarskog]

(ag***********@gmail.com) writes:

Now i execute the entire statement as

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

Still error comes that :
Unclosed quotation mark before the character string 'WITH MEMBER ..

('With member' is the starting statement of my MDX query)

Is there any other way to connect to OLAP server and execute an MDX
statement with a Length greater than 8000 chars

Have a look at http://www.sommarskog.se/dynamic_sql.html#OPENQUERY
for some tips.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx