Home Dashboard Directory Help
Search

run MDX query with parameters using OleDb by GregGalloway


Status: 

Active


10
0
Sign in
to vote
Type: Bug
ID: 251601
Opened: 1/13/2007 8:05:18 AM
Access Restriction: Public
0
Workaround(s)
view
6
User(s) can reproduce this bug

Description


I'm trying to run an MDX query against SSAS2005 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"); //changing it to @Param1 doesn't help
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();



Incidentally, 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
Details
Sign in to post a comment.
Posted by GregGalloway on 4/22/2013 at 10:08 AM
I'm reopening so this bug will be reconsidered for the next release.
Posted by Marco Russo on 12/31/2011 at 2:56 AM
I don't understand why this has been declared as fixed - SQL Server 2012 is coming and the bug is still present. A possible workaround is using Adomd instead. But I would appreciate if Microsoft would keep the bug open.
Posted by piers7 on 6/9/2010 at 5:47 AM
How is 'didn't meet the bar and was closed' the same as 'closed as fixed'

At very least this should be 'closed as external' and you could hide behind blaming the ADO.Net team.
Posted by Microsoft on 4/7/2008 at 6:31 PM
This bug didn't meet the bar for 2008 and it's been closed. Apologies for the inconvenience.

Posted by GregGalloway on 1/7/2008 at 4:54 PM
Any update? It's been a year.
Posted by Microsoft on 1/29/2007 at 3:39 PM
Thank you for the clarification. I have investigated this issue further and it appears to be a problem in the .Net's System.Dada.OleDb, which doesn't pass to us all the parameter information. Our provider supports only named parameters but the .Net framework passes them to our provider as unnamed params. This results in our provider raising an error that invalid parameters are used (meaning "unnamed"). I have already contacted the .Net team about this. Until this is fixed, the only work-around that I can think of is to use ADODB or ADOMD. I understand this may be very limiting in some scenarios so we will try hard to get the fix done as soon as possible.
Posted by GregGalloway on 1/23/2007 at 3:46 PM
You are correct that the following VB.NET code works. But it uses ADODB which is COM. I need to be able to do the same thing using System.Data.OleDb with a .NET language, not COM interop.

I can't find a NamedParameters property on the OleDbCommand object. Suggestions?

        Dim MDX As String = "with member [Measures].[Test] as Str(@Param1) " _
            & "SELECT [Measures].[Test] on 0, " _
            & "[Product].[Category].[Category].Members on 1 " _
            & "from [Adventure Works]"

        Dim conn As ADODB.Connection = New ADODB.Connection()
        conn.Open("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;")
        Dim cmd As ADODB.Command = New ADODB.Command()
        cmd.CommandText = MDX
        cmd.NamedParameters = True
        cmd.ActiveConnection = conn
        cmd.CommandType = CommandTypeEnum.adCmdText

        Dim param As ADODB.Parameter = cmd.CreateParameter()
        param.Direction = ParameterDirectionEnum.adParamInput
        param.Name = "Param1"
        param.Value = 2
        param.Type = DataTypeEnum.adInteger
        param.Size = 4
        cmd.Parameters.Append(param)

        Dim r As ADODB.Recordset = cmd.Execute()
        Console.WriteLine(r.Fields(0).Value)
        r.Close()
        conn.Close()
Posted by Microsoft on 1/23/2007 at 1:50 PM
I believe your usage of parametrized query in OLEDB is not entirely correct. Please, refer to the following example:

Having a query with a parameter such as "@typeParam" the following piece of code is necessary:

46 Cmd.NamedParameters = True
47
48 Dim typeParameter As ADODB.Parameter
49 Set typeParameter = Cmd.CreateParameter()
50 typeParameter.Direction = adParamInput
51 typeParameter.Name = "typeParam"
52 typeParameter.Value = 2
53 typeParameter.Type = adInteger
54 typeParameter.Size = 4
55
56 Cmd.Parameters.Append typeParameter

The following steps are important when using parameters with the OLE DB provider for Analysis Services:

The Command must be notified to use named parameters (line 46). The OLE DB provider for Analysis Services only supports named parameters.
The parameters must have a name that matches the indicator used in the query, less the @ prefix (line 51).
Only input parameters are supported (line 50).
Type and Size of a parameter should be declared (lines 53, 54).

- - -

To set the parameter attributes as arguments in a single function rather than doing it in separate lines as above, use CreateParameter function as described in the following article:
http://msdn2.microsoft.com/de-de/library/ms677209.aspx

Sign in to post a workaround.