Post reply

Using OpenRowSet to issue MDX Query

  • 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

  • August 6, 2010 at 8:57 pm

    #1203824

    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.

  • August 7, 2010 at 8:30 am

    #1203857

    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

  • August 7, 2010 at 8:37 am

    #1203858

    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

  • August 7, 2010 at 12:11 pm

    #1203874

    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.

  • August 9, 2010 at 11:48 am

    #1204315

    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

  • August 9, 2010 at 3:35 pm

    #1204435

    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.

  • August 11, 2010 at 10:40 am

    #1205520

    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

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

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