Using OpenRowSet to issue MDX Query

Question

Using OpenRowSet to issue MDX Query

Don Shields-368041

Ten Centuries

Points: 1189
More actions
August 6, 2010 at 4:41 pm

#222281

In order to build a Dynamcs MDX query I am forces to use TSQL to build the query and issue an openrowset command to run the query agains my SSAS2008 db (USING SQL Sever 2005 database engine for TSQL).
My problem is that I am running up agains the 8000 char limit on the length of my MDX. I store the text of the MDX in an SQL local variable of type varchar(max) and use exec(@strsql) to execute the openrowset command.
Any ideas on how to get past the 8000 char limit?
Thanks,
Don

Viewing 8 posts - 1 through 7 (of 7 total)

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

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Don,

Can we assume that something is forcing you to use TSQL rather than SSRS which will allow you to use parameters and possibly end-around the requirement for dynamic queries? You can also create dynamic queries within SSRS but again, if you're being forced out of there, it doesn't matter anyway.

If you're forced to use TSQL because you're working with a custom app (ie one that you're creating), is there any way you can use ADOMD.net versus ADO.net (or whatever you're using to pass the TSQl to the SQL server)?

Assuming none of the above sounds any good, you could always move to sp_executesql instead of 'exec(@parm)' which has like a 2gb limit on the data being passed as the query text (assuming you're on 64-bit) :hehe:

HTH,

Steve.

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 2

yes, I'm forced to use TSQL because you can't have multi-valued parameteres based on MDX queries in SSRS. For instance I have an Item Dimension that I want to limit to certain items. (I'm forced to use SSRS 2005).

Don

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 3

Out of curiosity how would you recommend executing dynamic mdx from withing SSRS? I was unable to get it to work using a string variable to hold the mdx in SSRS.

Don

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

Last time I did dynamic MDX was in the original SSRS release but this article[/url] did basically what we had to do (we didn't take it to the embedded code though).

Didn't read this one but looks to cover the same topic.

Steve.

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 5

Steve,

I have switched to sp_executesql as you recommended. now the statement: sp_executesql @strsql

executes ok but in SSRS I get the error "The character string that starts w/ 'select ......' is too long. Maximum length is 8000. The interesting thing is that the part about the statemtn that starts with .... is not the beginning of @strsql. The part that is mentioned in the error is only the portion of sql which is the parameter for the openrowset. the full @strsql is something like:

select * from openrowset('MSOLAP.3','DATASOURCE=jubilee\ssas2k8;Initial Catalog=CFBS_AX_OLAP;Integrated Security=SSPI', N'select ....')

To me this seems as if the sp_executesql does not have a problem with the string > 8000 char but the openrowset does.

Don

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

Hey Don,

Could only find where people (msft or not) reference there *not* being a limit t that query size. One thing I did see was that if the linked server is running Out of Process (and option on the linked server) then it can cause a query to fail. Can you check what state yours is set to?

Steve.

Don Shields-368041 Ten Centuries Points: 1189 More actions · Answer 7

Thanks for all the input steve. I was unable to solve the issue w/ Openrowset but I was able to create a c# datamethod that calls SSAS directly and executes the query. So I put my code for generating the MDX in there and did it without using T-SQL.

I have another issue but I'll start another post for that one.

Don