MDX with parameters using OleDb?

I'm trying to run an MDX query with a parameter. It works fine with ADOMD.NET, but I can't get it to work using OleDb. (I have to use OleDb because of limitations of the calling application.) Is this possible?

The following code works:

//using Microsoft.AnalysisServices.AdomdClient;

string MDX = "with member [Measures].[Test] as Str(@.Param1) "

+ "SELECT [Measures].[Test] on 0, "

+ "[Product].[Category].[Category].Members on 1 "

+ "from [Adventure Works]";

AdomdConnection conn = new AdomdConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");

conn.Open();

AdomdCommand cmd = new AdomdCommand(MDX, conn);

cmd.Parameters.Add("Param1", "abcde");

System.Data.DataSet ds = new System.Data.DataSet();

AdomdDataAdapter adp = new AdomdDataAdapter(cmd);

adp.Fill(ds);

Console.WriteLine(ds.Tables[0].Rows[0][1]);

conn.Close();

The following code fails:

//using System.Data.OleDb;

string MDX = "with member [Measures].[Test] as Str(@.Param1) "

+ "SELECT [Measures].[Test] on 0, "

+ "[Product].[Category].[Category].Members on 1 "

+ "from [Adventure Works]";

OleDbConnection conn = new OleDbConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");

conn.Open();

OleDbCommand cmd = new OleDbCommand(MDX, conn);

cmd.Parameters.AddWithValue("Param1", "abcde");

System.Data.DataSet ds = new System.Data.DataSet();

OleDbDataAdapter adp = new OleDbDataAdapter(cmd);

adp.Fill(ds); //produces error: "The following system error occurred:The parameter is incorrect. . Error Code = 0x80070057, External Code = 0x00000000:."

Console.WriteLine(ds.Tables[0].Rows[0][1]);

conn.Close();

Try to add @. in front of the parameter name. It seems that ADOMD.NET and OledbClient are not constent in this regard.

cmd.Parameters.AddWithValue("@.Param1", "abcde");

|||

Irina-

I still get the same error even after adding the @. sign. If that code works for you, what version are you running on? Is this maybe an SP2 fix?

|||

Actually I'm getting the same error. Debugging OLEDB provider shows that OledbClient passes it

DBPARAMBINDINFO with NULL parameter name. So it seems like a bug in OledbClient. Currently I don't have access to OledbClient sourses. I'll try to find sorces tomorrow, but can't promise, so may be you should ask the same question on OledbClient forum.

|||

I cross-posted in the forum (I think) you were talking about:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1088407&SiteID=1&mode=1

|||

I haven't heard anything from the other forum, so I'm afraid I'm never going to get an answer on this. Irina, have you had a chance to look at the source? Or should I post this to connect.microsoft.com?

I also ran across the following Books Online page which mentions that you're required to use the ICommandWithParameters interface when doing parameterized MDX with OleDb. I'm not optimistic that will produce any different results, and I haven't really found a good code sample that shows how to implement that interface and use it.
http://msdn2.microsoft.com/en-us/library/ms145518.aspx

|||

reported to connect:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=251601