ADOMD.NET Sample

Hi,
Anybody tells me about any sample application available for ADOMD.NET.
Regards
StephenThere is a webcast available in Microsoft discussing about this topic-
Webcast:
http://msevents.microsoft.com/CUI/E...e=en-U
S
Also I remember seeing an article in SqlMag.com while ago about using
MD.NET...
- Prabhu
"BI-Solutions" wrote:

> Hi,
> Anybody tells me about any sample application available for ADOMD.NET.
> Regards
> Stephen
>
>

ADOMD.NET DbProviderFactory

Currently, the codebase that I use makes use of provider independent data access code using the DbProviderFactory classes. Does the ADOMD.NET library have a data provider that can be accessed via System.Data.Common.DbProviderFactories? Microsoft.AnalysisServices.AdomdClient is not currently listed as an installed data provider on my machine despite the installation of said assembly.
Moved it from BCL forum to Data Access and Storage forum where is more appropriate.
Thank you for your active participation!

ADOMD.NET Compression Not Working

I am using AdomdConnection to connect to analysis services over http through the msmdpump.dll in IIS. Here's my connection string...

connectionString="Provider=MSOLAP.3;user id=auserid;password=apassword;Data Source=http://servername/olap/msmdpump.dll; Initial Catalog=CatalogName; Transport Compression=Compressed; Compression Level=9;"

Everything works but some of the cellsets returned are large and I need compression. It is not returning a compressed http response. When I sniff the http request I do not see 'Accept-Encoding: gzip,deflate'. If I hit a regular web page with IE I see this in the http request headers and the content returned is compressed.

Any ideas anyone?

Thanks ahead of time.

Rich

This is a known problem and will be fixed in SP1.

_-_-_ Dave

ADOMD.net Can't get the KPI.ID or Cube.ID

When you open an AS project, you will see every object has an ID property, just like KPI.ID and cube.ID and Dimention.ID. And that's diffrent of the Name property. You could chanage the name property to everything you like, but when you created an object, you could not change it's ID.

When i use the ADOMD.NET, I could do this:

Dim myKPIConnection As AdomdConnection
Dim myCubeDef As CubeDef
Dim k As Kpi

myConnectionString = "Data Source=" + myOlapServer + ";Catalog=" + myOlapDatabase + ";Provider=MSOLAP;"

myKPIConnection = New AdomdConnection(myConnectionString)
myKPIConnection.Open()

For i = 0 To myKPIConnection.Cubes.Count - 1

If myKPIConnection.Cubes(i).Type = CubeType.Cube Then

myCubeDef = myKPIConnection.Cubes(i)

k = myKPIConnection.Cubes(i).Kpis(0)

MessageBox.Show(k.Name)

MessageBox.Show(k.Caption)

end if
next

But the question is, there's no k.ID or cubes(i).ID property there!

The k.Name and k.Caption both refer to the kpi's Name property in the AS server in factly.

Is that means we could not use the object 's ID property in the ADOMD.NET?

Another question, in MDX, we could only refer a dimention by it's name, and could not by it's ID, right?

ivanchain wrote:

Is that means we could not use the object 's ID property in the ADOMD.NET?

Another question, in MDX, we could only refer a dimention by it's name, and could not by it's ID, right?

That's right. In ADOMD.NET and MDX all references are done by name. The ID property is mainly used by the administrative APIs

adomd.net cache server object

Hi All,

I have a bit of a dilemma regarding how to cache the server object so I don't have to reconnect all the time.

I have a web service which serves AS2005 data, and whenever the cube schema changes (or sometimes just after a process), the server object doesn't return the cubes collection, and I end up having to restart IIS or the app to clear the application obj and start again. Obviously this is not the best. Is there anyway I can detect for schema changes in the server object, and serve up a fresh obj if there has been?

I'm using the following code:

Code Snippet

Dim server As OlapServer.Server = Me.Application.Get(cubeServerStorageKey)
If (server Is Nothing) Then

Dim cubeServer As String = System.Configuration.ConfigurationManager.AppSettings.Get("Analysis Server")
If (String.IsNullOrEmpty(cubeServer)) Then _
cubeServer = "."

Dim cubeServerTimeoutSeconds As String = System.Configuration.ConfigurationManager.AppSettings.Get("Cube Timeout")

server = New OlapServer.Server(cubeServer, Me.RequestData.Database, Int32.Parse(cubeServerTimeoutSeconds))
server.Connect()

Me.Application.Set(cubeServerStorageKey, server)
End If

Return server

hello,

could you please clarify a bit. I believe adomd.net does not have an OlapServer class in it (nor does AMO). So, can you please provide relevant code (i guess of how the OlapServer object returns you the cubes collection, since from the problem description you mention that this is where the problem occurs), because without that code it is hard to tell what the problem might be or how to solve it.

thanks a lot,

|||

Well why would you want to cache such a connection? 100ms routine is hardly worthy of cache. Chaching the return dataset I can see, but connection? Sorry if I misunderstood.

Also, you can use .NET's cache methods to specify an exact timeout for a cached object.

Adomd.net and ClickOnce

We need to install ADOMD.NET on client workstations and would like to install it via ClickOnce. Has anyone built a ClickOnce Bootstrapper Prerequisite Package for SQLServer2005_ADOMD.MSI?

Also, when running SQLServer2005_ADOMD.MSI on a client workstation manually, we receive an error stating that an updated version of MSXML 6.0 must be installed as a prerequisite. We run the MSXML6.MSI (11/7/2005) found in the SS05 November 2005 Feature Pack and then run SQLServer2005_ADOMD.MSI and everything is fine. This workstation already has Version 2.0 of the .NET Framework installed on it. Isn't MSXML 6.0 a prerequisite for installing V 2.0 of the .NET framework?

If we need to install the updated version of MSXML 6.0 prior to installing ADOMD.NET, has anyone built a ClickOnce Bootstrapper Prerequisite Package for the updated version of MSXML 6.0?

We realize these components might be part of the SQL Server 2005 Express Edition Prerequisite Package that is available, but would like to avoid an additional 35 mg download if at all possible.

Thanks in advance for your help.

Dont know much about ClickOnce.

Some answers for you:

You are right ADOMD.NET requires MSXML6 to be present. If it is not installed the setup will prompt you.

.NET framework does not include the MSXML 6 component.

I doubt ADOMD.NET is part of SQL Express installation package.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

ADOMD.NET AdomdDataReader + ASP.NET 2.0 gridview?

Anybody had any success with populating an ASP.NET 2.0 gridview using ADOMD.NET AdomdDataReader?

I am trying the following code:

Dim oSb AsNew StringBuilder

Dim sMDX AsString

Dim sCnnString AsString = "DataSource=localhost"

Dim oAdoMdCnn As AdomdConnection

Dim oAdoMdCmd As AdomdCommand

Dim oAdoMdRdr As AdomdDataReader

'...build oSb

sMDX = oSb.ToString

sCnnString = "DataSource=localhost"

oAdoMdCnn = New AdomdConnection(sCnnString)

oAdoMdCnn.Open()

oAdoMdCmd = New AdomdCommand(sMDX, oAdoMdCnn)

oAdoMdRdr = oAdoMdCmd.ExecuteReader()

Dim oTable AsNew DataTable

oTable.Load(oAdoMdRdr)

gvResults.DataSource = oTable

gvResults.DataBind()

I get an error message at oTable.Load(oAdoMdRdr) that says:

"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

This gives a couple of options for datasets, but no mention of datareaders nevertheless AdomdDataReaders. I have tried "oTable.Constraints.Clear()" to eliminate any constraints, but no luck. I don't want any keys or constraints. I don't need any writeback so I think using a dataset may be more overhead than necessary.

Anyone?

Keehan

hello Keehan,

it's not competely clear to me what the problem is here (it might be specific to the query result, so if you can provide a query against Adventure Works sample that this error happens with, then perhaps it could shed some light here).

however, you could also try something like:

Dim oTable As New DataTable()

// where cmd is the command to execute
Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(oTable)
End Using

and see if that works to populate the table.

hope this helps,

|||

Mary,

I tried using AdomdDataAdapter as you recommended and I get an even weirder message:

InvalidOperationException was unhandled by user code

“The connection cannot be used while an XmlReader object is open.”

While debugging I found that if I look at the properties of adapter, the Message attribute of the SelectCommand property says:

Message"Unable to cast object of type 'Microsoft.AnalysisServices.AdomdClient.AdomdCommand' to type 'System.Data.Common.DbCommand'."String

And the StackTrace attribute says:

StackTrace"at System.Data.Common.DbDataAdapter.get_SelectCommand()"String

I know the MDX query itself works fine.I’m capturing it to a text box and then I can run it from Management Studio.I’m just having trouble getting the plumbing of this to work.Any other ideas?

If anyone has a sample using ADOMD.NET to populate a gridview I would be grateful.

Cheers,

Keehan

|||

hello Keehan,

actually, the error you observe indicates that a data reader (or xml reader) were not closed. It means that somewhere in the code there is a call to cmd.ExecuteReader(), but the returned reader is never closed or disposed. It is very impornant that the reader is closed otherwise the error would be thrown just as the one you observed. (so it is good idea to work with reader with Using or try-finally to make sure it's always closed/disposed)

the following code snippet works fine for me, populating the DataTable (resultsInTable) with data fine:

Dim resultsInTable As New DataTable()

Using con As New AdomdConnection()

con.ConnectionString = "datasource=localhost;catalog=Adventure Works DW;"
con.Open()

Dim cmd As AdomdCommand
cmd = con.CreateCommand()
cmd.CommandText = "select measures.members on 0, [Customer].[Customer Geography].[Country].members on 1 from [Adventure Works]"

Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(resultsInTable)
End Using

End Using

so, i'm not sure why it would not work for you. maybe something else happens when different data comes back (obviously when different query is executed), but i couldn't tell not knowing more specifics.

also, it looks like the GridView with AutoGenerateColumns=true, has some limitations as to how the columns of type System.Object are handled - i think they are not added to grid view (or maybe i was doing something wrong - as i'm not too familiar with the System.Web.UI.WebControls.GridView). so it might be that for the example above one would have to write some more code to actually create columns in the grid view for the un-bindable column types, or do some other things: like tweaking the data table converting the un-bindable data types to strings or something else...

all in all for the sample above the following code worked for me (note code is not too clean, and is just intended as an illustration; i'm also not too familiar with VB):

Imports Microsoft.AnalysisServices.AdomdClient
Imports System.Data

Partial Class _Default
Inherits System.Web.UI.Page

Dim gv As GridView

Protected Sub Page_Load1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim resultsInTable As New DataTable()

Using con As New AdomdConnection()
con.ConnectionString = "datasource=localhost;catalog=Adventure Works DW;"
con.Open()

Dim cmd As AdomdCommand
cmd = con.CreateCommand()
cmd.CommandText = "select measures.members on 0, [Customer].[Customer Geography].[Country].members on 1 from [Adventure Works]"

Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(resultsInTable)
End Using

End Using

' because looks like GridView has limitations and only supports certain column types (and not not object typed one for autogenerate columns)
' let's do the following : for each not supported column type - add a string typed column

Dim numberColumns As Integer
numberColumns = resultsInTable.Columns.Count - 1
For i As Integer = 0 To numberColumns

Dim current As DataColumn
current = resultsInTable.Columns.Item(i)
Dim colName As String

Dim baseName = "column_"
'you might need to be smarter about picking the base of the column name,
'as column names based on it must not be present in the table already

If BaseDataList.IsBindableType(current.DataType) = False Then
colName = current.ColumnName
current.ColumnName = baseName + i.ToString() ' might need to be smarter and check if the name already exists in the table
Dim col As New DataColumn(colName, System.Type.GetType("System.String"), "Convert(" + current.ColumnName + ", 'System.String')")
resultsInTable.Columns.Add(col)
End If
Next i

gv.DataSource = resultsInTable
gv.DataBind()

End Sub

Protected Sub form1_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Init
gv = New GridView()
gv.AutoGenerateColumns = True
form1.Controls.Add(gv)

End Class

hope this helps,

|||Hello Mary,

i was having the problem that when a i bound the return of an mdx command to a gridView, the columns simply doesn′t appear.

i searched at the web and i found your solution, i'm just interesting to know if there is another way to show the columns in the gridView, without handling them as you did, i mean if there is another component, another alternative, i know that this solutions works ( i tested it :P ) , but maybe there is another way, and since your post was made quite a long time, maybe you or other person, have another solution.

Thanx.|||

hello,

unfortunatelly, i don't have more info on this. I would suggest you to post a question on "Data Presentation Controls" on ASP.NET forum (http://forums.asp.net/24/ShowForum.aspx), and ask about the support for columns of System.Object type with AutoGenerateColumns=true. Perhaps there are other control(s), or maybe there are some settings that can make the GridView work in this scenario. Hopefully they would be able to answer.

hope this helps,

|||

You need to turn off constraints at the DataSet level. You have simply tried to clear existing constraints at the table level.

Here's a C# example

DataSet dataSet = new DataSet(); // Create a dataset

dataSet.EnforceConstraints = false; // turn off constraints

dataSet.Tables.Add("Results"); // Add an arbitary table

dataSet.Tables["Results"].Load(reportDataReader); // Load the ADOMD reader into the table

You will then be able to bind the 'Results' table to a gridview.

Hope this helps

Sacha Tomey

Blog - http://blogs.adatis.co.uk/blogs/sachatomey

Consultancy - http://www.adatis.co.uk

ADOMD.NET AdomdDataReader + ASP.NET 2.0 gridview?

Anybody had any success with populating an ASP.NET 2.0 gridview using ADOMD.NET AdomdDataReader?

I am trying the following code:

Dim oSb As New StringBuilder

Dim sMDX As String

Dim sCnnString As String = "DataSource=localhost"

Dim oAdoMdCnn As AdomdConnection

Dim oAdoMdCmd As AdomdCommand

Dim oAdoMdRdr As AdomdDataReader

'...build oSb

sMDX = oSb.ToString

sCnnString = "DataSource=localhost"

oAdoMdCnn = New AdomdConnection(sCnnString)

oAdoMdCnn.Open()

oAdoMdCmd = New AdomdCommand(sMDX, oAdoMdCnn)

oAdoMdRdr = oAdoMdCmd.ExecuteReader()

Dim oTable As New DataTable

oTable.Load(oAdoMdRdr)

gvResults.DataSource = oTable

gvResults.DataBind()

I get an error message at oTable.Load(oAdoMdRdr) that says:

"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

This gives a couple of options for datasets, but no mention of datareaders nevertheless AdomdDataReaders. I have tried "oTable.Constraints.Clear()" to eliminate any constraints, but no luck. I don't want any keys or constraints. I don't need any writeback so I think using a dataset may be more overhead than necessary.

Anyone?

Keehan

hello Keehan,

it's not competely clear to me what the problem is here (it might be specific to the query result, so if you can provide a query against Adventure Works sample that this error happens with, then perhaps it could shed some light here).

however, you could also try something like:

Dim oTable As New DataTable()

// where cmd is the command to execute
Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(oTable)
End Using

and see if that works to populate the table.

hope this helps,

|||

Mary,

I tried using AdomdDataAdapter as you recommended and I get an even weirder message:

InvalidOperationException was unhandled by user code

“The connection cannot be used while an XmlReader object is open.”

While debugging I found that if I look at the properties of adapter, the Message attribute of the SelectCommand property says:

Message"Unable to cast object of type 'Microsoft.AnalysisServices.AdomdClient.AdomdCommand' to type 'System.Data.Common.DbCommand'."String

And the StackTrace attribute says:

StackTrace"at System.Data.Common.DbDataAdapter.get_SelectCommand()"String

I know the MDX query itself works fine.I’m capturing it to a text box and then I can run it from Management Studio.I’m just having trouble getting the plumbing of this to work.Any other ideas?

If anyone has a sample using ADOMD.NET to populate a gridview I would be grateful.

Cheers,

Keehan

|||

hello Keehan,

actually, the error you observe indicates that a data reader (or xml reader) were not closed. It means that somewhere in the code there is a call to cmd.ExecuteReader(), but the returned reader is never closed or disposed. It is very impornant that the reader is closed otherwise the error would be thrown just as the one you observed. (so it is good idea to work with reader with Using or try-finally to make sure it's always closed/disposed)

the following code snippet works fine for me, populating the DataTable (resultsInTable) with data fine:

Dim resultsInTable As New DataTable()

Using con As New AdomdConnection()

con.ConnectionString = "datasource=localhost;catalog=Adventure Works DW;"
con.Open()

Dim cmd As AdomdCommand
cmd = con.CreateCommand()
cmd.CommandText = "select measures.members on 0, [Customer].[Customer Geography].[Country].members on 1 from [Adventure Works]"

Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(resultsInTable)
End Using

End Using

so, i'm not sure why it would not work for you. maybe something else happens when different data comes back (obviously when different query is executed), but i couldn't tell not knowing more specifics.

also, it looks like the GridView with AutoGenerateColumns=true, has some limitations as to how the columns of type System.Object are handled - i think they are not added to grid view (or maybe i was doing something wrong - as i'm not too familiar with the System.Web.UI.WebControls.GridView). so it might be that for the example above one would have to write some more code to actually create columns in the grid view for the un-bindable column types, or do some other things: like tweaking the data table converting the un-bindable data types to strings or something else...

all in all for the sample above the following code worked for me (note code is not too clean, and is just intended as an illustration; i'm also not too familiar with VB):

Imports Microsoft.AnalysisServices.AdomdClient
Imports System.Data

Partial Class _Default
Inherits System.Web.UI.Page

Dim gv As GridView

Protected Sub Page_Load1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim resultsInTable As New DataTable()

Using con As New AdomdConnection()
con.ConnectionString = "datasource=localhost;catalog=Adventure Works DW;"
con.Open()

Dim cmd As AdomdCommand
cmd = con.CreateCommand()
cmd.CommandText = "select measures.members on 0, [Customer].[Customer Geography].[Country].members on 1 from [Adventure Works]"

Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(resultsInTable)
End Using

End Using

' because looks like GridView has limitations and only supports certain column types (and not not object typed one for autogenerate columns)
' let's do the following : for each not supported column type - add a string typed column

Dim numberColumns As Integer
numberColumns = resultsInTable.Columns.Count - 1
For i As Integer = 0 To numberColumns

Dim current As DataColumn
current = resultsInTable.Columns.Item(i)
Dim colName As String

Dim baseName = "column_"
'you might need to be smarter about picking the base of the column name,
'as column names based on it must not be present in the table already

If BaseDataList.IsBindableType(current.DataType) = False Then
colName = current.ColumnName
current.ColumnName = baseName + i.ToString() ' might need to be smarter and check if the name already exists in the table
Dim col As New DataColumn(colName, System.Type.GetType("System.String"), "Convert(" + current.ColumnName + ", 'System.String')")
resultsInTable.Columns.Add(col)
End If
Next i

gv.DataSource = resultsInTable
gv.DataBind()

End Sub

Protected Sub form1_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Init
gv = New GridView()
gv.AutoGenerateColumns = True
form1.Controls.Add(gv)

End Class

hope this helps,

|||Hello

Mary,

i was having the problem that when a i bound the return of an mdx command to a gridView, the columns simply doesn′t appear.

i searched at the web and i found your solution, i'm just interesting to know if there is another way to show the columns in the gridView, without handling them as you did, i mean if there is another component, another alternative, i know that this solutions works ( i tested it :P ) , but maybe there is another way, and since your post was made quite a long time, maybe you or other person, have another solution.

Thanx.|||

hello,

unfortunatelly, i don't have more info on this. I would suggest you to post a question on "Data Presentation Controls" on ASP.NET forum (http://forums.asp.net/24/ShowForum.aspx), and ask about the support for columns of System.Object type with AutoGenerateColumns=true. Perhaps there are other control(s), or maybe there are some settings that can make the GridView work in this scenario. Hopefully they would be able to answer.

hope this helps,

|||

You need to turn off constraints at the DataSet level. You have simply tried to clear existing constraints at the table level.

Here's a C# example

DataSet dataSet = new DataSet(); // Create a dataset

dataSet.EnforceConstraints = false; // turn off constraints

dataSet.Tables.Add("Results"); // Add an arbitary table

dataSet.Tables["Results"].Load(reportDataReader); // Load the ADOMD reader into the table

You will then be able to bind the 'Results' table to a gridview.

Hope this helps

Sacha Tomey

Blog - http://blogs.adatis.co.uk/blogs/sachatomey

Consultancy - http://www.adatis.co.uk

ADOMD.NET AdomdDataReader + ASP.NET 2.0 gridview?

Anybody had any success with populating an ASP.NET 2.0 gridview using ADOMD.NET AdomdDataReader?

I am trying the following code:

Dim oSb As New StringBuilder

Dim sMDX As String

Dim sCnnString As String = "DataSource=localhost"

Dim oAdoMdCnn As AdomdConnection

Dim oAdoMdCmd As AdomdCommand

Dim oAdoMdRdr As AdomdDataReader

'...build oSb

sMDX = oSb.ToString

sCnnString = "DataSource=localhost"

oAdoMdCnn = New AdomdConnection(sCnnString)

oAdoMdCnn.Open()

oAdoMdCmd = New AdomdCommand(sMDX, oAdoMdCnn)

oAdoMdRdr = oAdoMdCmd.ExecuteReader()

Dim oTable As New DataTable

oTable.Load(oAdoMdRdr)

gvResults.DataSource = oTable

gvResults.DataBind()

I get an error message at oTable.Load(oAdoMdRdr) that says:

"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

This gives a couple of options for datasets, but no mention of datareaders nevertheless AdomdDataReaders. I have tried "oTable.Constraints.Clear()" to eliminate any constraints, but no luck. I don't want any keys or constraints. I don't need any writeback so I think using a dataset may be more overhead than necessary.

Anyone?

Keehan

hello Keehan,

it's not competely clear to me what the problem is here (it might be specific to the query result, so if you can provide a query against Adventure Works sample that this error happens with, then perhaps it could shed some light here).

however, you could also try something like:

Dim oTable As New DataTable()

// where cmd is the command to execute
Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(oTable)
End Using

and see if that works to populate the table.

hope this helps,

|||

Mary,

I tried using AdomdDataAdapter as you recommended and I get an even weirder message:

InvalidOperationException was unhandled by user code

“The connection cannot be used while an XmlReader object is open.”

While debugging I found that if I look at the properties of adapter, the Message attribute of the SelectCommand property says:

Message"Unable to cast object of type 'Microsoft.AnalysisServices.AdomdClient.AdomdCommand' to type 'System.Data.Common.DbCommand'."String

And the StackTrace attribute says:

StackTrace"at System.Data.Common.DbDataAdapter.get_SelectCommand()"String

I know the MDX query itself works fine.I’m capturing it to a text box and then I can run it from Management Studio.I’m just having trouble getting the plumbing of this to work.Any other ideas?

If anyone has a sample using ADOMD.NET to populate a gridview I would be grateful.

Cheers,

Keehan

|||

hello Keehan,

actually, the error you observe indicates that a data reader (or xml reader) were not closed. It means that somewhere in the code there is a call to cmd.ExecuteReader(), but the returned reader is never closed or disposed. It is very impornant that the reader is closed otherwise the error would be thrown just as the one you observed. (so it is good idea to work with reader with Using or try-finally to make sure it's always closed/disposed)

the following code snippet works fine for me, populating the DataTable (resultsInTable) with data fine:

Dim resultsInTable As New DataTable()

Using con As New AdomdConnection()

con.ConnectionString = "datasource=localhost;catalog=Adventure Works DW;"
con.Open()

Dim cmd As AdomdCommand
cmd = con.CreateCommand()
cmd.CommandText = "select measures.members on 0, [Customer].[Customer Geography].[Country].members on 1 from [Adventure Works]"

Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(resultsInTable)
End Using

End Using

so, i'm not sure why it would not work for you. maybe something else happens when different data comes back (obviously when different query is executed), but i couldn't tell not knowing more specifics.

also, it looks like the GridView with AutoGenerateColumns=true, has some limitations as to how the columns of type System.Object are handled - i think they are not added to grid view (or maybe i was doing something wrong - as i'm not too familiar with the System.Web.UI.WebControls.GridView). so it might be that for the example above one would have to write some more code to actually create columns in the grid view for the un-bindable column types, or do some other things: like tweaking the data table converting the un-bindable data types to strings or something else...

all in all for the sample above the following code worked for me (note code is not too clean, and is just intended as an illustration; i'm also not too familiar with VB):

Imports Microsoft.AnalysisServices.AdomdClient
Imports System.Data

Partial Class _Default
Inherits System.Web.UI.Page

Dim gv As GridView

Protected Sub Page_Load1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim resultsInTable As New DataTable()

Using con As New AdomdConnection()
con.ConnectionString = "datasource=localhost;catalog=Adventure Works DW;"
con.Open()

Dim cmd As AdomdCommand
cmd = con.CreateCommand()
cmd.CommandText = "select measures.members on 0, [Customer].[Customer Geography].[Country].members on 1 from [Adventure Works]"

Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(resultsInTable)
End Using

End Using

' because looks like GridView has limitations and only supports certain column types (and not not object typed one for autogenerate columns)
' let's do the following : for each not supported column type - add a string typed column

Dim numberColumns As Integer
numberColumns = resultsInTable.Columns.Count - 1
For i As Integer = 0 To numberColumns

Dim current As DataColumn
current = resultsInTable.Columns.Item(i)
Dim colName As String

Dim baseName = "column_"
'you might need to be smarter about picking the base of the column name,
'as column names based on it must not be present in the table already

If BaseDataList.IsBindableType(current.DataType) = False Then
colName = current.ColumnName
current.ColumnName = baseName + i.ToString() ' might need to be smarter and check if the name already exists in the table
Dim col As New DataColumn(colName, System.Type.GetType("System.String"), "Convert(" + current.ColumnName + ", 'System.String')")
resultsInTable.Columns.Add(col)
End If
Next i

gv.DataSource = resultsInTable
gv.DataBind()

End Sub

Protected Sub form1_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Init
gv = New GridView()
gv.AutoGenerateColumns = True
form1.Controls.Add(gv)

End Class

hope this helps,

|||Hello

Mary,

i was having the problem that when a i bound the return of an mdx command to a gridView, the columns simply doesn′t appear.

i searched at the web and i found your solution, i'm just interesting to know if there is another way to show the columns in the gridView, without handling them as you did, i mean if there is another component, another alternative, i know that this solutions works ( i tested it :P ) , but maybe there is another way, and since your post was made quite a long time, maybe you or other person, have another solution.

Thanx.|||

hello,

unfortunatelly, i don't have more info on this. I would suggest you to post a question on "Data Presentation Controls" on ASP.NET forum (http://forums.asp.net/24/ShowForum.aspx), and ask about the support for columns of System.Object type with AutoGenerateColumns=true. Perhaps there are other control(s), or maybe there are some settings that can make the GridView work in this scenario. Hopefully they would be able to answer.

hope this helps,

|||

You need to turn off constraints at the DataSet level. You have simply tried to clear existing constraints at the table level.

Here's a C# example

DataSet dataSet = new DataSet(); // Create a dataset

dataSet.EnforceConstraints = false; // turn off constraints

dataSet.Tables.Add("Results"); // Add an arbitary table

dataSet.Tables["Results"].Load(reportDataReader); // Load the ADOMD reader into the table

You will then be able to bind the 'Results' table to a gridview.

Hope this helps

Sacha Tomey

Blog - http://blogs.adatis.co.uk/blogs/sachatomey

Consultancy - http://www.adatis.co.uk

ADOMD.NET AdomdDataReader + ASP.NET 2.0 gridview?

Anybody had any success with populating an ASP.NET 2.0 gridview using ADOMD.NET AdomdDataReader?

I am trying the following code:

Dim oSb As New StringBuilder

Dim sMDX As String

Dim sCnnString As String = "DataSource=localhost"

Dim oAdoMdCnn As AdomdConnection

Dim oAdoMdCmd As AdomdCommand

Dim oAdoMdRdr As AdomdDataReader

'...build oSb

sMDX = oSb.ToString

sCnnString = "DataSource=localhost"

oAdoMdCnn = New AdomdConnection(sCnnString)

oAdoMdCnn.Open()

oAdoMdCmd = New AdomdCommand(sMDX, oAdoMdCnn)

oAdoMdRdr = oAdoMdCmd.ExecuteReader()

Dim oTable As New DataTable

oTable.Load(oAdoMdRdr)

gvResults.DataSource = oTable

gvResults.DataBind()

I get an error message at oTable.Load(oAdoMdRdr) that says:

"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

This gives a couple of options for datasets, but no mention of datareaders nevertheless AdomdDataReaders. I have tried "oTable.Constraints.Clear()" to eliminate any constraints, but no luck. I don't want any keys or constraints. I don't need any writeback so I think using a dataset may be more overhead than necessary.

Anyone?

Keehan

hello Keehan,

it's not competely clear to me what the problem is here (it might be specific to the query result, so if you can provide a query against Adventure Works sample that this error happens with, then perhaps it could shed some light here).

however, you could also try something like:

Dim oTable As New DataTable()

// where cmd is the command to execute
Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(oTable)
End Using

and see if that works to populate the table.

hope this helps,

|||

Mary,

I tried using AdomdDataAdapter as you recommended and I get an even weirder message:

InvalidOperationException was unhandled by user code

“The connection cannot be used while an XmlReader object is open.”

While debugging I found that if I look at the properties of adapter, the Message attribute of the SelectCommand property says:

Message"Unable to cast object of type 'Microsoft.AnalysisServices.AdomdClient.AdomdCommand' to type 'System.Data.Common.DbCommand'."String

And the StackTrace attribute says:

StackTrace"at System.Data.Common.DbDataAdapter.get_SelectCommand()"String

I know the MDX query itself works fine.I’m capturing it to a text box and then I can run it from Management Studio.I’m just having trouble getting the plumbing of this to work.Any other ideas?

If anyone has a sample using ADOMD.NET to populate a gridview I would be grateful.

Cheers,

Keehan

|||

hello Keehan,

actually, the error you observe indicates that a data reader (or xml reader) were not closed. It means that somewhere in the code there is a call to cmd.ExecuteReader(), but the returned reader is never closed or disposed. It is very impornant that the reader is closed otherwise the error would be thrown just as the one you observed. (so it is good idea to work with reader with Using or try-finally to make sure it's always closed/disposed)

the following code snippet works fine for me, populating the DataTable (resultsInTable) with data fine:

Dim resultsInTable As New DataTable()

Using con As New AdomdConnection()

con.ConnectionString = "datasource=localhost;catalog=Adventure Works DW;"
con.Open()

Dim cmd As AdomdCommand
cmd = con.CreateCommand()
cmd.CommandText = "select measures.members on 0, [Customer].[Customer Geography].[Country].members on 1 from [Adventure Works]"

Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(resultsInTable)
End Using

End Using

so, i'm not sure why it would not work for you. maybe something else happens when different data comes back (obviously when different query is executed), but i couldn't tell not knowing more specifics.

also, it looks like the GridView with AutoGenerateColumns=true, has some limitations as to how the columns of type System.Object are handled - i think they are not added to grid view (or maybe i was doing something wrong - as i'm not too familiar with the System.Web.UI.WebControls.GridView). so it might be that for the example above one would have to write some more code to actually create columns in the grid view for the un-bindable column types, or do some other things: like tweaking the data table converting the un-bindable data types to strings or something else...

all in all for the sample above the following code worked for me (note code is not too clean, and is just intended as an illustration; i'm also not too familiar with VB):

Imports Microsoft.AnalysisServices.AdomdClient
Imports System.Data

Partial Class _Default
Inherits System.Web.UI.Page

Dim gv As GridView

Protected Sub Page_Load1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim resultsInTable As New DataTable()

Using con As New AdomdConnection()
con.ConnectionString = "datasource=localhost;catalog=Adventure Works DW;"
con.Open()

Dim cmd As AdomdCommand
cmd = con.CreateCommand()
cmd.CommandText = "select measures.members on 0, [Customer].[Customer Geography].[Country].members on 1 from [Adventure Works]"

Using adapter As New AdomdDataAdapter(cmd)
adapter.Fill(resultsInTable)
End Using

End Using

' because looks like GridView has limitations and only supports certain column types (and not not object typed one for autogenerate columns)
' let's do the following : for each not supported column type - add a string typed column

Dim numberColumns As Integer
numberColumns = resultsInTable.Columns.Count - 1
For i As Integer = 0 To numberColumns

Dim current As DataColumn
current = resultsInTable.Columns.Item(i)
Dim colName As String

Dim baseName = "column_"
'you might need to be smarter about picking the base of the column name,
'as column names based on it must not be present in the table already

If BaseDataList.IsBindableType(current.DataType) = False Then
colName = current.ColumnName
current.ColumnName = baseName + i.ToString() ' might need to be smarter and check if the name already exists in the table
Dim col As New DataColumn(colName, System.Type.GetType("System.String"), "Convert(" + current.ColumnName + ", 'System.String')")
resultsInTable.Columns.Add(col)
End If
Next i

gv.DataSource = resultsInTable
gv.DataBind()

End Sub

Protected Sub form1_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Init
gv = New GridView()
gv.AutoGenerateColumns = True
form1.Controls.Add(gv)

End Class

hope this helps,

|||Hello Mary,

i was having the problem that when a i bound the return of an mdx command to a gridView, the columns simply doesn′t appear.

i searched at the web and i found your solution, i'm just interesting to know if there is another way to show the columns in the gridView, without handling them as you did, i mean if there is another component, another alternative, i know that this solutions works ( i tested it :P ) , but maybe there is another way, and since your post was made quite a long time, maybe you or other person, have another solution.

Thanx.|||

hello,

unfortunatelly, i don't have more info on this. I would suggest you to post a question on "Data Presentation Controls" on ASP.NET forum (http://forums.asp.net/24/ShowForum.aspx), and ask about the support for columns of System.Object type with AutoGenerateColumns=true. Perhaps there are other control(s), or maybe there are some settings that can make the GridView work in this scenario. Hopefully they would be able to answer.

hope this helps,

|||

You need to turn off constraints at the DataSet level. You have simply tried to clear existing constraints at the table level.

Here's a C# example

DataSet dataSet = new DataSet(); // Create a dataset

dataSet.EnforceConstraints = false; // turn off constraints

dataSet.Tables.Add("Results"); // Add an arbitary table

dataSet.Tables["Results"].Load(reportDataReader); // Load the ADOMD reader into the table

You will then be able to bind the 'Results' table to a gridview.

Hope this helps

Sacha Tomey

Blog - http://blogs.adatis.co.uk/blogs/sachatomey

Consultancy - http://www.adatis.co.uk

AdoMd.Net 9.0 and AS2000

Hi,
I'm trying to connect to AS2000 using AdoMd.Net 9.0 and I get the following
error:
{"Retrieving the COM class factory for component with CLSID
{B9776FC2-70D8-4664-A0DF-998114524D67} failed due to the following error:
80040154."}
Trying to connect using AdoMd.Net 8.0 (same code, same connection string)
works just fine.
Runtime version: v2.0.50727
AdoMd.Net version: v9.0.242.0
AS2000 version: SP4
I've read that AdoMd.Net 9.0 should be able to connect to AS2000 with no
problem. Can someone point me in the right direction?
Thanks a lot in advance,
E
Ok, figured it out.
The file msadomdx.dll was not in the 90 sub-directory of the AdoMd.Net main
directory.
Thanks.
"Elad" <elad&&&7690@.hot&&&mail.com> wrote in message
news:OwRgmilmHHA.3996@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I'm trying to connect to AS2000 using AdoMd.Net 9.0 and I get the
> following error:
> {"Retrieving the COM class factory for component with CLSID
> {B9776FC2-70D8-4664-A0DF-998114524D67} failed due to the following error:
> 80040154."}
> Trying to connect using AdoMd.Net 8.0 (same code, same connection string)
> works just fine.
> Runtime version: v2.0.50727
> AdoMd.Net version: v9.0.242.0
> AS2000 version: SP4
> I've read that AdoMd.Net 9.0 should be able to connect to AS2000 with no
> problem. Can someone point me in the right direction?
> Thanks a lot in advance,
> E
>

ADOMD.net 9.0 (client) and CubeDef.LastProcessed

What is this property (CubeDef.LastProcessed) supposed to return as it does not return the date the cube was last processed?

Does AMO provide this info more reliably ?

TIA
Dave

Hi Dave,

It's a bug - CubeDef.LastProcessed returns the last time the server was restarted as far as I know. See
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606

...for more details. Take a look at the comment posted by furmangg on April 28th on this entry on my blog for some code which does what you want:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!675.entry

It's also available in the Analysis Services Stored Procedure Project here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures&title=CubeInfo

HTH,

Chris

|||

Thanks Chris,

Its not resolved in SP1 either... Im going to check out AMO.

Cheers
Dave

|||FWIW AMO works for AS2005 when retrieving the last processed datetime. Doesnt work for AS2000 or local cubes.

ADOMD.net 9.0 (client) and CubeDef.LastProcessed

What is this property (CubeDef.LastProcessed) supposed to return as it does not return the date the cube was last processed?

Does AMO provide this info more reliably ?

TIA
Dave

Hi Dave,

It's a bug - CubeDef.LastProcessed returns the last time the server was restarted as far as I know. See
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606

...for more details. Take a look at the comment posted by furmangg on April 28th on this entry on my blog for some code which does what you want:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!675.entry

It's also available in the Analysis Services Stored Procedure Project here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures&title=CubeInfo

HTH,

Chris

|||

Thanks Chris,

Its not resolved in SP1 either... Im going to check out AMO.

Cheers
Dave

|||FWIW AMO works for AS2005 when retrieving the last processed datetime. Doesnt work for AS2000 or local cubes.

ADOMD.NET 8.0 dependencies for connecting to both AS 2000 and AS 2005

I've got a C# application developed with Visual Studio .Net 2003 which uses Adomd.net (8.0) to access cubes on SQL Server 2000 Analysis Services as well as SQL Server 2005 Analysis Services. In the MSDN reference page I noticed that I can set the connection parameter "ConnectTo=Default", and now, after installing the SQL Server 2005 Client Connectivity components, I can make connections to both AS2K and AS2K5 from my development machine (XP SP2).

However, I can't figure out the dependencies to make this work on other systems. On another XP SP2 system (pretty bare-bones), if I install either the SQL Server 2000 or 2005 Client Connectivity components, I can connect to AS2K5, but when I try connecting to AS2K, I get:

AdomdConnectionException: A connection cannot be made. Ensure that the server is running. --> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it.

Following another thread in this forum, I tried installing PTSLITE for AS2K, but that had no effect.

On another XP SP2 system, with SQL Server 2000 client components, AS2K connections work but AS2K5 connections fail silently.

On a Win2K machine, AS2K5 connections work, but trying AS2K results in "AdomdErrorResponseException: The provider could not determine the value."

Can anyone tell me the essential prerequisites for making both connections? Thanks!

There are several components that are getting invoked to establish connection to different servers. You might need to try to see if each one of the is working separately.

1. Connection to AS 2005.

You can use ADOMD.NET 9 by installing Microsoft ADOMD.NET

from http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

2. Connection to AS 2000

ADOMD.NET is using AS OLEDB 8.0 ( MSOLAP.2) OLE DB provider to connect to AS 2000.

After installing ptslite.exe (Microsoft SQL Server 2000 PivotTable Services ) from http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en you can test if you can establish connection to AS 2000 by using MDX Sample application ( shipped with AS 2000)

ADOMD.NET v 9 is avaliable for download from the same page.

For your reference. The installation folder for ADOMD.NET is %SystemDrive%\Program Files\Microsoft.NET\ADOMD.NET\

There you fill find 80 or 90 sub-folders for correspodingly ADOMD.NET v8 and v9.

Hope using this information you will be able to troublshoot what is going on.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

Thanks for the response.

However, I believe ADOMD.NET 9.0 depends on Framework 2.0, and I'm not ready to upgrade to Visual Studio 2005 just yet. I've been able to connect to AS2005 without it anyway.

I tried downloading the latest version of ptslite, but it still says the AS 2000 server "actively refused the connection".

|||

Also, does the .NET Framework cache the OLEDB providers? I've tried unregistering/renaming C:\Program Files\Common Files\System\Oledb\msolap80.dll to verify dependence, but the application connectivity still works the same!

I'm still not close to getting dual connectivity to work properly, so any suggestions are appreciated!

|||

To troubleshoot connectivity issues I would start from trying to establish connection to AS2000 using MDX Sample appication.
It is going to use AS OLEDB 8 ( MSOLAP.2 ) to connect.

See if you can copy MDX Sample app to your client machine and you can establish connection from there.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

Well, I'm making a little progress. I finally realized that one test XP system wasn't able to connect to AS 2000 because I wasn't logged into it with a domain account, so it didn't have permission to access the cubes. That doesn't explain why it was able to access our AS 2005 system, though!

That brings me to the issue of my Windows 2000 test system. Logged into it with a domain account, the MDX Sample App works fine, but my .NET application still can't connect to AS 2000. (It still says "Exception: The provider could not determine the value.") It does connect to AS 2005, though. I verified in C:\Winnt\assembly that Microsoft.AnalysisServices.AdomdClient is version 8.0.700, the same as on my XP systems.

Any further suggestions? Thanks!

|||

hello Jon,

the list of pre-requisites for the adomd.net 8.0 is (as from the readme file):

* Microsoft .NET Framework Class Library 1.0 SP2 or greater

* MSXML 4.0 or greater

* AS2000 OLE DB provider required for Microsoft Analysis Services 2000 data access

so, i'd check on msxml4.0 if msolap80 is definitelly present.

if that does not help, wrap up the connection.Open() into following block of code, to see if it could reveal more information about the error:

try

{

// code here

}

catch (Exception e)

{

Exception ex = e;

while (ex != null)

{

Debug.WriteLine("========Exception================");

Debug.WriteLine("Type: " + ex.GetType().FullName);

Debug.WriteLine("Message: " + ex.Message);

Debug.WriteLine("Stack :" + ex.StackTrace);

AdomdErrorResponseException errResponse = ex as

AdomdErrorResponseException;

AdomdConnectionException conException = ex as

AdomdConnectionException;

if (errResponse != null)

{

foreach (AdomdError r in errResponse.Errors)

{

Debug.WriteLine("::::ERROR::::");

Debug.WriteLine("code: " +

r.ErrorCode.ToString());

Debug.WriteLine("msg: " + r.Message);

}

}

else if (conException != null)

{

Debug.WriteLine("ExceptionCause:" +

conException.ExceptionCause.ToString());

}

ex = ex.InnerException;

}

} // catch

hope this helps,

|||

Thanks for the response, but I don't think those prerequisites are correct. I installed the PTSLITE package, which include msolap80.dll, but it didn't make any difference in the connectivity behavior on my one Windows 2003 system. Also, I ran the application in the debugger on XP and when a connection was made to AS 2000, the list of loaded modules did not include msolap80.dll or msxml4.dll.

I did find some more unexpected behavior, though: on both Windows 2000 and Wndows 2003, I'm not able to connect to some AS 2000 servers (running on Win2K), but I tried another AS 2000 server running on Windows 2003, and the connection works! When the connection fails, the error information is:

========Exception================

Type: Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException

Message: The provider could not determine the value.

Stack : at Microsoft.AnalysisServices.AdomdClient.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection+IXmlaClientProviderEx.Discover(String requestType, String requestNamespace, IDictionary restrictions, Boolean throwOnErrors)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetSchemaDataSet(String schemaName, String schemaNamespace, IDictionary adomdRestrictions)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetSchemaDataSet(Guid schema, Object[] restrictions)
at DataSupport.OlapReader.GetOlapCatalogs(String source, Boolean useWinSecurity, String userID, String password)

::::ERROR::::

code: 8

msg: The provider could not determine the value

|||

hello Jon,

according to the stack trace, it looks like you were able to connect, but what fails is the schema rowset request (it seems that the call is to the GetSchemaDataSet....., not conneciton.Open)? is it so? if so, could you please provide more details as to the actual schema request, and restrictions provided?

thank you,

|||

You're right, I guess the connection.Open() succeeds, and it's GetSchemaDataSet() that doesn't behave consistently on all instances. We call it with the constant AdomdSchemaGuid.Catalogs and an empty array of restrictions.

I notice in the reference page for AdomdSchemaGuid a note that "Some members of the AdomdSchemaGuid class (such as the CATALOGS schema rowset) may not be supported by your provider." But shouldn't this be consistent on all installations of SQL Server 2000 Analysis Services and all clients? As I said originally, my Win XP development system can connect to (and get the list of catalogs from) all AS instances, and one Win 2003 system can do the same for all AS 2000 systems, but a Win 2000 system and one Win 2003 system can't get the list of catlogs for some AS 2000 systems (running on Win 2000).

The reference page also says, "Refer to your database documentation to determine whether you can retrieve this schema information using other techniques." What other techniques might work better?

|||

hello Jon,

good thing is that we now seems to have determined exactly which operation fails, and that connection seems to succeed.

yes, i think Catalogs schema should work with AS. it is not yet clear what exacty is wrong here. could you please paste the exact connection string you have for adomd.net when this fails. Also could you please clarify whether you connect to same AS server (i mean you say from some machines getting catalogs succeeds but from other fails, so what i'm trying to understand is whether you try to connect to same server in both cases). Also, is it the same user that connects (i.e. does it have same rights when successfull and when failure ?)

Also, just to try isolating the problem more, could you please run the following code and see what happens (and post all error details if failure happens):

try
{
using (OleDbConnection connection = new OleDbConnection())
{
connection.ConnectionString = "Provider=MSOLAP.2;<your same connection string as for adomd.net>";
connection.Open();

// should be similar restrictions and schema as for the adomd.net code
DataTable table = connection.GetOleDbSchemaTable(AdomdSchemaGuid.Catalogs, new object[0] { });
}
}
catch (Exception ex)
{
while (ex != null)
{
Debug.WriteLine("========Exception================");
Debug.WriteLine("Type: " + ex.GetType().FullName);
Debug.WriteLine("Message: " + ex.Message);
Debug.WriteLine("Stack :" + ex.StackTrace);

if (ex is OleDbException)
{
OleDbException oledb = ex as OleDbException;
foreach (OleDbError r in oledb.Errors)
{
Debug.WriteLine("::::ERROR::::");
Debug.WriteLine("code: " + r.NativeError.ToString());
Debug.WriteLine("msg: " + r.Message);
}
}
ex = ex.InnerException;
}

}

thank you,

|||

Mary:

The connection string we're using with the AdomdConnection object is "Data Source=as2kserver;Integrated Security=SSPI;ConnectTo=Default". It is the same in all cases, whether the GetSchemaDataSet() succeeds or not.

Trying the OleDbConnection method, I get the following error message for the servers that fail:

========OLEDB Exception==========
Type: System.InvalidOperationException
Message: The provider could not determine the Object value. For example, the row was just created, the default for the Object column was not available, and the consumer had not yet set a new Object value.
Stack : at System.Data.OleDb.DBBindings.get_Value()
at System.Data.OleDb.OleDbDataReader.GetValues(Object[] values)
at System.Data.OleDb.OleDbDataReader.DumpToTable(OleDbConnection connection, IRowset rowset)
at System.Data.OleDb.OleDbConnection.GetSchemaRowset(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
at DataSupport.OlapReader.GetOlapCatalogs(String source, Boolean useWinSecurity, String userID, String password)

|||

hello Jon,

then i have to conclude that this is somehow related to msolap80 (oledb provider for AS2000), since the code above used it directly and also fails with same error. so this does not seem like adomd.net specific issue. (adomd.net essentially uses msolap80 when working with AS2000)

in this case i would expect that MDXSample application when run from same box and connecting to same AS server should also fail enumerating catalogs. does it happen indeed?

can you double check on msolap80: is it properly registered? what's it's version? is the version the same as msolap80 has on the box that successfully enumerates catalogs?

thank you,

|||

Mary:

I just ran MDXSample on my Windows 2000 system and it connected to my AS 2000 server (on Win2000) and listed catalogs and cubes successfully, but my .NET application with your code using OleDb still failed in GetOleDbSchemaTable() with the same "provider could not determine the Object value" error. Both accessed another AS 2000 server (running on Windows 2003) successfully.

On that Win2000 client system, C:\WINNT\assembly shows Microsoft.AnalysisServices.AdomdClient as version 8.0.700.0, while the File version and Product Version of C:\Program Files\Microsoft.NET\Adomd.NET\80\Microsoft.AnalysisServices.AdomdClient.dll show as 8.0.702.0. When I search in regedit for "MSOLAP.2", it finds C:\Program Files\Common Files\System\OLE DB\msolap80.dll.

It seems like there's some slight difference between my AS 2000 servers that is only noticeable from a .NET application.

|||

Jon,

i don't have a clear picture of what's wrong exactly (apart from it being related to msolap80 itself, since the test with OleDbConnection.....), but i guess one needs to find what is different in the case when failure happens from when it succeeds.

i'd check on the following:

1. whether the same version (service pack) is installed: between the AS2000 server that the app fails against, and the AS2000 server that you said the app succeeds working with.

2. check on the version of ptslite that you installed on the box from which app works fine, and on the box from which connection to one of the AS2000 server does not work.

3. try to see if you have some client machine from which the app succeeds against that AS 2000 server (on Win2000) (against which the client machine in question fails).

4. maybe .net is different on the box from which client works ok comparing to .net version on the box where client app fails? (btw. is the app asp.net or winforms/console ? if it's asp.net, i'd try a small winforms/console app with just that oledb code to see if that makes any difference)

sorry for not being able to pinpoint the issue faster, but i think the key is to try and spot the difference between the boxes where it works and where it does not.

thanks,

ADOMD.NET : dynamic parameters

Logically speaking, the two cases below should behave the same. However case 2's output is wrong. Perhaps someone knows what's wrong in case 2.

Case 1:
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] " +

" UNION SELECT @.var1 as [var] " +

" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

Case 2
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] ";

for(int i=1; i<3; i++)

{

cmd.CommandText = cmd.CommandText +

"UNION SELECT @.var" + i.ToString() + " as [var] ";

}

cmd.CommandText = cmd.CommandText + ") AS [vartable]) AS t";

Mary

Can you get a debug print of the resulting command text?

Actually, the best way to solve this problem is to use rowset parameters. There is a sample here: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

(look for // Issue a DMX query from a rowset parameter and display a result)

For nested tables you will have to SHAPE the rowset.

|||

I've read this article:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

The problem in my case is that I have to use the "UNION" keyword.
How to use rowset parameters for such case?

My case
--
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" UNION SELECT @.var1 as [var] " +
" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

The above link has only a solution for this case
-
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" SELECT @.var1 as [var] " +
" SELECT @.var2 as [var]) AS [vartable]) AS t";

// Suggested solution for the above case (not my case):
DataTable table = new DataTable();
table.Columns.Add("term0", System.Type.GetType("System.String"));
table.Columns.Add("term1", System.Type.GetType("System.String"));
table.Columns.Add("term2", System.Type.GetType("System.String"));
...
...

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" @.InputTable as t";
cmd.Parameters.Add("InputTable", table);

Clearly, the provided solution not applicable for my case.
Please assist!

Mary

|||

So, basically, the problem is that your input is nested, right? It contains the [vartable] nested table.

You can try this:

DataTable topTable = new DataTable();
topTable.Columns.Add("K", typeof(System.Int32)); // case key

// Add one row to the case table with K=1
object[] row = new object[1];
row[0] = 1; // top key
topTable.Rows.Add(row);

DataTable nestedTable = new DataTable();
nestedTable.Columns.Add("K", typeof(System.Int32)); // foreign key
table.Columns.Add("var", System.Type.GetType("System.String"));

// Add one row to nested table for each @.var value
// each nested row has 1 as foreign key
row = new object[2];

for( ... )
{
row[0] = 1; // foreign key, alway one to match the topTable single row's key
row[1] = "aaa"; // foreign key
nestedTable.Rows.Add(row);
}

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" SHAPE {@.topTable} " +
" APPEND( {@.nestedTable} RELATE K TO K) "+
" AS vartable AS T";

cmd.Parameters.Add("topTable", topTable);
cmd.Parameters.Add("nestedTable", nestedTable);

ADOMD.NET : dynamic parameters

Logically speaking, the two cases below should behave the same. However case 2's output is wrong. Perhaps someone knows what's wrong in case 2.

Case 1:
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] " +

" UNION SELECT @.var1 as [var] " +

" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

Case 2
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] ";

for(int i=1; i<3; i++)

{

cmd.CommandText = cmd.CommandText +

"UNION SELECT @.var" + i.ToString() + " as [var] ";

}

cmd.CommandText = cmd.CommandText + ") AS [vartable]) AS t";

Mary

Can you get a debug print of the resulting command text?

Actually, the best way to solve this problem is to use rowset parameters. There is a sample here: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

(look for // Issue a DMX query from a rowset parameter and display a result)

For nested tables you will have to SHAPE the rowset.

|||

I've read this article:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

The problem in my case is that I have to use the "UNION" keyword.
How to use rowset parameters for such case?

My case
--
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" UNION SELECT @.var1 as [var] " +
" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

The above link has only a solution for this case
-
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" SELECT @.var1 as [var] " +
" SELECT @.var2 as [var]) AS [vartable]) AS t";

// Suggested solution for the above case (not my case):
DataTable table = new DataTable();
table.Columns.Add("term0", System.Type.GetType("System.String"));
table.Columns.Add("term1", System.Type.GetType("System.String"));
table.Columns.Add("term2", System.Type.GetType("System.String"));
...
...

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" @.InputTable as t";
cmd.Parameters.Add("InputTable", table);

Clearly, the provided solution not applicable for my case.
Please assist!

Mary

|||

So, basically, the problem is that your input is nested, right? It contains the [vartable] nested table.

You can try this:

DataTable topTable = new DataTable();
topTable.Columns.Add("K", typeof(System.Int32)); // case key

// Add one row to the case table with K=1
object[] row = new object[1];
row[0] = 1; // top key
topTable.Rows.Add(row);

DataTable nestedTable = new DataTable();
nestedTable.Columns.Add("K", typeof(System.Int32)); // foreign key
table.Columns.Add("var", System.Type.GetType("System.String"));

// Add one row to nested table for each @.var value
// each nested row has 1 as foreign key
row = new object[2];

for( ... )
{
row[0] = 1; // foreign key, alway one to match the topTable single row's key
row[1] = "aaa"; // foreign key
nestedTable.Rows.Add(row);
}

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" SHAPE {@.topTable} " +
" APPEND( {@.nestedTable} RELATE K TO K) "+
" AS vartable AS T";

cmd.Parameters.Add("topTable", topTable);
cmd.Parameters.Add("nestedTable", nestedTable);