AMO:Problem when dropping a role from Analysis Server 2005 by using .Net Programming and AMO

HI All,

I am working on Analysis services 2005 and managing with AMO in .Net 2.0.

Here am able to create a role and drop a role if there is no permissions to the role but in case role is having any permissions am not able to Drop the role from the database.Could any one please tell me how to remove the permissions on that role by using AMO and .Net.

Thanks in Advance,

vishwesh

Have you tried searching the DatabasePermissions collection of the database for Permissions referring to the Role in question, and removing each of these from the database?

Programming AMO Security Objects

...

A role cannot be dropped if there is any Permission object associated with the role. To drop a role, all Permission objects in the Database objects must be searched, and the Role removed from permissions, before the Role can be dropped from the Database.

|||

Hi

I gone through the document but can you send sample code how to remove the database permissions .

|||

I don't have such a working sample at the moment - maybe someone else on the Forum does?

AMO:Problem when dropping a role from Analysis Server 2005 by using .Net Programming and AMO

HI All,

I am working on Analysis services 2005 and managing with AMO in .Net 2.0.

Here am able to create a role and drop a role if there is no permissions to the role but in case role is having any permissions am not able to Drop the role from the database.Could any one please tell me how to remove the permissions on that role by using AMO and .Net.

Thanks in Advance,

vishwesh

Have you tried searching the DatabasePermissions collection of the database for Permissions referring to the Role in question, and removing each of these from the database?

Programming AMO Security Objects

...

A role cannot be dropped if there is any Permission object associated with the role. To drop a role, all Permission objects in the Database objects must be searched, and the Role removed from permissions, before the Role can be dropped from the Database.

|||

Hi

I gone through the document but can you send sample code how to remove the database permissions .

|||

I don't have such a working sample at the moment - maybe someone else on the Forum does?

AMO:Can any one knows how to drop the permissions

HI all,

I am working on AMO and .NET.In my application i have to create a role and that i am able to do it.When i am creating a role i provided some Database and cube permissions.while dropping the role i am getting some error message is like permissions are exist on that object(ROLE).So what i did is i dropped permissions first after that i am tried to drop the role.

Still i am getting the same problem when i am dropping the permissions.I am sending that part of code which i am using in my application.

dbPerm = amodatabase.DatabasePermissions.FindByRole(RoleName)
dbPerm.Drop(DropOptions.IgnoreFailures)
cubePerm = amocube.CubePermissions.FindByRole(RoleName)
cubePerm.Drop(DropOptions.IgnoreFailures)
Newrole.Drop(DropOptions.IgnoreFailures)

after dropping the role i am commiting the transaction.and i am getting below error message while deleting.

Errors in the metadata manager. The cube permission with the ID of 'CubePermission 3', Name of 'CubePermission 3' was invalidated by operations in the transaction. Errors in the metadata manager. The transaction cannot be committed because one or more objects were invalidated during the transaction

Please help me on this and correct my code.

Regards,

vishwesh

Set Newrole.Drop(DropOptions.IgnoreFailures) to Newrole.Drop(DropOptions.AlterOrDeleteDependents).

I believe you can remove the following:

dbPerm = amodatabase.DatabasePermissions.FindByRole(RoleName)
dbPerm.Drop(DropOptions.IgnoreFailures)
cubePerm = amocube.CubePermissions.FindByRole(RoleName)
cubePerm.Drop(DropOptions.IgnoreFailures)

B.

|||

Hi Bryan,

I tried with the above code and executes but i am getting some strange error see below.

Errors in the metadata manager. Impact analysis inside a pending transaction cannot support CREATE, DELETE, or ALTER commands.

could you please let me know what could be the problem.

Regards,

vishweshwar

|||

Could you post your revised code?

Thanks,
B.

|||

Hi Bryan,

Please find the revised code.

Newrole = amodatabase.Roles.FindByName(RoleName)
If Newrole Is Nothing Then
Throw New ApplicationException("Role not exist in database")
Else
Newrole.Drop()
amodatabase.Update()
amoserver.CommitTransaction()
End If

Please help me on this how to sort it out.

|||

Bryan's suggestion was to use the AlterOrDeleteDependants option on the Drop method.

Newrole = amodatabase.Roles.FindByName(RoleName)
If Newrole Is Nothing Then
Throw New ApplicationException("Role not exist in database")
Else

Newrole.Drop(DropOptions.AlterOrDeleteDependents).

amodatabase.Update()
amoserver.CommitTransaction()
End If

AMO: Looping to delete partitions

Hi

I am trying - in AMO code - to loop over the partitions in my different measure groups and drop these. But apparently I am not allowed to do it with the following code, since I am modifying the collection which I am looping (because I drop the partitions which exist in the collection).

....

For Each oMeasuregroup in oCube.MeasureGroups

For Each oPartition in oMeasureGroup.Partitions

oPartition.Drop()

Next

Next

...

Can anyone help me with a solution? Thanks.

Hi,

You need to iterate over the partitions collection with integer index.

PartitionCollection partitions = ...;

for( int i=partitions.Count-1; i>=0; --i )
partitions[ i ].Drop();

Adrian Dumitrascu

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 Role drop trouble

I've some trouble trying to drop roles with AMO and the following code:

Role oCurrentRole = oOLAPDB.Roles.GetByName(<role name>);
oCurrentRole.Drop(DropOptions.AlterOrDeleteDependents);

Most of the time it's OK but I've sometimes this error

Exception non gre : System.InvalidOperationException: Impossible de supprimer l'objet ? Role ?my role , il doit faire partie d'un objet Server connect.
à Microsoft.AnalysisServices.MajorObject.DropPrivate(DropOptions options, XmlaWarningCollection warnings, ImpactDetailCollection impactResult, Boolean analyzeImpactOnly)
à Microsoft.AnalysisServices.MajorObject.Drop(DropOptions options)

(in english: Unhandled exception : System.InvalidOperationException: Unable to drop ? Role object ?my role , it must be part of a connected Server object)

I'm the only one connected to this server and I'm sure this role isn't used.

One thing to mention: It seems I've this error only with roles with members which are no more existing in the domain (they were when the role was created but get deleted later).

Any idea ? How could I drop these roles with AMO ?

David,

I created an account on my local system, JunkUser, and then created a role in Adventure Works DW called JunkRole. JunkUser is the lone member of this role. I then dropped the JunkUser account from my system.

In SSIS, I created a package with a single Script task. I added the AMO reference and an Imports statement to simplify my code. In Sub Main, I added this code:

Code Snippet

Dim MyServer As New Server

MyServer.Connect(".")

Dim MyDatabase As Database = MyServer.Databases.GetByName("Adventure Works DW")

Dim MyRole As Role = MyDatabase.Roles.GetByName("JunkRole")

MyRole.Drop(DropOptions.AlterOrDeleteDependents)

MyServer.Disconnect()

I executed the SSIS package. It completed successsfully and I verified the JunkRole had been dropped.

I am running on SP2.

Not sure what might else may be causing the problem you are seeing.

B.

|||My bad

You're right Bryan, it doens't matter if the user has been deleted or not

In fact, I had a nice
oServer.Disconnect();
just before my code... And of course, oServer is my OLAP Server object... Now that I'm thinking about it, the error message was quite explicit ("...connected Server")