AMO: refresh DSV and accept changes..

I tried the sql server OLAP newsgroup, but without success... this is fairly urgent, so will try in here:
I am stumped on this, how can I programmatically do what can be done in
Visual Studio by:
1. Right clicking inside datasource view
2. Choosing refresh
3. Accepting the changes

I am trying to get new fact table columns into the DSV, obviously
without doing the manual steps above.

I have tried dsv.Refresh() and dsv.Update() but it is not as simple as
that or the sequencing is incorrect (or, most likely, I am looking in
entirely the wrong place).

Thanks for any help.

There is no programmatiic way to do refresh on DSV. However, You can use data adapter to fill schema and get a new datatable and merge the table yourself.

For adding a column, you can use data adapter to do FillSchema() with the table you want to refresh and add the new column into existing table. Finally, call DSV.Update().

|||Thanks for the response - this was pretty much the only way we could solve this as well.... but why isn't the method that MS uses to do this exposed for our use as well? It seems like they have a pretty efficient way of doing this. Nearly everything else allows you to "script the action", why not this?
|||This refresh of DSV from relational DB is part of the execution of DSV but not on AMO. Although we found that it is useful if we move the code to AMO, it is to late of the cycle. Since it has work around, so we cut this feature. We will consider it in next release. Thanks

AMO: Can''t update dsv when delete a column

Hi, friends, please have a look at this:

I am using AMO, and I do this:

1 step: I drop a column from the source table by SQL:

ALTER TABLE TargetTable Drop COLUMN ColumnName

2 step: I try to update the dsv by AMO:

Dim adapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter( _
"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0", connection)
Dim i As Integer

For i = 0 To dsv.Schema.Tables.Count - 1
If dsv.Schema.Tables(i).TableName = "dbo_" & tableName Then
MessageBox.Show("Before dsv.Schema.Tables.Count:" & dsv.Schema.Tables(i).Columns.Count)
Dim dataTable As DataTable = adapter.FillSchema(dsv.Schema.Tables(i), SchemaType.Mapped)
MessageBox.Show("After dsv.Schema.Tables.Count:" & dsv.Schema.Tables(i).Columns.Count)
End If

Next

But, from the first messagebox and the second messagebox, I see the dsv is not updated after I delete the column.

3 step: I save the dsv to the server.

If Mainform.tDatabase.DataSourceViews.Count > 0 Then
Mainform.tDatabase.DataSourceViews(0).Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull)
End If

Then I check the server, the dsv still include the columnname I have deleted.

Why and how to update the dsv after I delete a column?

Thanks!

ivanchain wrote:

Dim dataTable As DataTable = adapter.FillSchema(dsv.Schema.Tables(i), SchemaType.Mapped)

ivanchain wrote:

But, from the first messagebox and the second messagebox, I see the dsv is not updated after I delete the column

Let's also check if the returned 'dataTable' still contains the column you deleted. If it does contain the column, then we need to double check the table name and its columns in SQL Server.If the returned 'dataTable' doesn't contain the column, it looks like you need to replace the table in the DSV with this returned 'dataTable', but according to documentation at http://msdn2.microsoft.com/en-us/library/152bda9x.aspx, this should not be the case.

The rest of the code looks good, the problem is not in AMO, but in the FillSchema area.

Adrian Dumitrascu

|||

I tried what you said:

Let's also check if the returned 'dataTable' still contains the column you deleted. If it does contain the column, then we need to double check the table name and its columns in SQL Server.

Yes, the returned 'dataTable' still contains the column I deleted. But I don't know what you exactly mean of DOUBLE CHECK the table name and its columns in SQL Server? I need to check what?

Thank you!

|||

The problem is still there.... help!

thanks.

|||

The only ideas that I have are:

- double check that the database name (that you use in the code) is the same with the database on which you removed the column from the table. There might be a concidence that you have 2 databases containing the same table name and column name, you deleted from one, but the code works on the other database by chance (since I don't see in the code where you explicitly chose the database on which to run the SELECT statement)

- double check that the name and the schema, 'dbo', of the table you use in the code are the same as the schema and the name of the table from which you deleted the column

|||

Thanks, Adrian. But, I don't think it's about the NAME of the table. Because my code could update dsv when I add a column into the SQL table in the SQL Server. If the name of the table is wrong, it will also not update when adding, right?

Thanks!

AMO: Can't update dsv when delete a column

Hi, friends, please have a look at this:

I am using AMO, and I do this:

1 step: I drop a column from the source table by SQL:

ALTER TABLE TargetTable Drop COLUMN ColumnName

2 step: I try to update the dsv by AMO:

Dim adapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter( _
"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0", connection)
Dim i As Integer

For i = 0 To dsv.Schema.Tables.Count - 1
If dsv.Schema.Tables(i).TableName = "dbo_" & tableName Then
MessageBox.Show("Before dsv.Schema.Tables.Count:" & dsv.Schema.Tables(i).Columns.Count)
Dim dataTable As DataTable = adapter.FillSchema(dsv.Schema.Tables(i), SchemaType.Mapped)
MessageBox.Show("After dsv.Schema.Tables.Count:" & dsv.Schema.Tables(i).Columns.Count)
End If

Next

But, from the first messagebox and the second messagebox, I see the dsv is not updated after I delete the column.

3 step: I save the dsv to the server.

If Mainform.tDatabase.DataSourceViews.Count > 0 Then
Mainform.tDatabase.DataSourceViews(0).Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull)
End If

Then I check the server, the dsv still include the columnname I have deleted.

Why and how to update the dsv after I delete a column?

Thanks!

ivanchain wrote:

Dim dataTable As DataTable = adapter.FillSchema(dsv.Schema.Tables(i), SchemaType.Mapped)

ivanchain wrote:

But, from the first messagebox and the second messagebox, I see the dsv is not updated after I delete the column

Let's also check if the returned 'dataTable' still contains the column you deleted. If it does contain the column, then we need to double check the table name and its columns in SQL Server.If the returned 'dataTable' doesn't contain the column, it looks like you need to replace the table in the DSV with this returned 'dataTable', but according to documentation at http://msdn2.microsoft.com/en-us/library/152bda9x.aspx, this should not be the case.

The rest of the code looks good, the problem is not in AMO, but in the FillSchema area.

Adrian Dumitrascu

|||

I tried what you said:

Let's also check if the returned 'dataTable' still contains the column you deleted. If it does contain the column, then we need to double check the table name and its columns in SQL Server.

Yes, the returned 'dataTable' still contains the column I deleted. But I don't know what you exactly mean of DOUBLE CHECK the table name and its columns in SQL Server? I need to check what?

Thank you!

|||

The problem is still there.... help!

thanks.

|||

The only ideas that I have are:

- double check that the database name (that you use in the code) is the same with the database on which you removed the column from the table. There might be a concidence that you have 2 databases containing the same table name and column name, you deleted from one, but the code works on the other database by chance (since I don't see in the code where you explicitly chose the database on which to run the SELECT statement)

- double check that the name and the schema, 'dbo', of the table you use in the code are the same as the schema and the name of the table from which you deleted the column

|||

Thanks, Adrian. But, I don't think it's about the NAME of the table. Because my code could update dsv when I add a column into the SQL table in the SQL Server. If the name of the table is wrong, it will also not update when adding, right?

Thanks!

AMO - Memory Error !!! PLEASE HELP

based on the Adventure Works example i decide to create the DSV, when i call the method ' AddTable ' as follows:

-

private void AddTable(Microsoft.AnalysisServices.DataSourceView dsv, OleDbConnection connection, String tableName)
{
OleDbDataAdapter adapter = new OleDbDataAdapter(
"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0",
connection);
DataTable[] dataTables = adapter.FillSchema(dataSet, SchemaType.Mapped, tableName);
DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add("TableType", "Table");
dataTable.ExtendedProperties.Add("DbSchemaName", "dbo");
dataTable.ExtendedProperties.Add("DbTableName", tableName);
}

-

when i try to create the Dim product calling this function i got this error:

Memory error: While attempting to store a string, a string

was found that was larger than the page size selected. The operation

cannot be completed.
Errors in the OLAP storage engine: An error

occurred while the 'LargePhoto' attribute of the 'DimProduct' dimension

from the 'teste2005' database was being processed.
Errors in the

OLAP storage engine: The process operation ended because the number of

errors encountered during processing reached the defined limit of

allowable errors for the operation.

-

And i know that this functions works well because if i comment the creation of DimProduct, everything works fine, so the problem is with some atributes, like ' LargePhoto' for example,

How can i fix this memory error ?, i read some posts in the forum, but i didn't get a concrete answer,

PLEASE HELP !!!!!This may help:

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

C