AMO: Can't update dsv when delete a column

ivanchain

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!

Adrian Dumitrascu

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

ivanchain

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!

ivanchain

The problem is still there.... help!

thanks.

Adrian Dumitrascu

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

ivanchain

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!