How to delete the role by using AMO(Analysis Management Object)?

I want to delete the role by using AMO, but what I find is only a way that how to create it.

following sample is deleting the members of the role :

Code Snippet

Dim ServerName As Server 'Connect OLAP Server
Dim db As Database 'Database of OLAP Server

Dim role As Role

role = db.Roles.Item(0)
role.Members.Clear()
role.Update()

So I think that deleting the roles of database is like following :

Code Snippet

db.Roles.Clear()

db.Update()

It's wrong. It can't delete roles of a database after executing.

Please tell me what I shall do or where I can find these infoemation, thanks!!

Here's a code sample from one of our developers that does this. (Thanks for the code, Jason.) Keep in mind that in this code, we were looking for Roles that met a naming standard. It's a long story, but you may want to skip that step. Still, I kept it in here (and only changed the naming pattern) so I can insure the code still works with minimal effort. Also, keep in mind this code was written for an SSIS package so you will see some odd ball references in there.

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

Dim roleCollection As New ArrayList()

' FIND ALL OF THE ROLES IN THE ANALYSIS SERVICES DB THAT MATCH THE NAMING PATTERN
For Each currentRole As Role In database.Roles
If (currentRole.Name.StartsWith("XYZ")) Then
' BECAUSE YOU CAN'T DELETE AN ITEM IN A COLLECTION, ADD TO THE roleCollection ARRAY LIST
roleCollection.Add(currentRole)
End If
Next

' DELETE ALL ROLES IN THE roleCollection
For Each currentObj As Role In roleCollection

'DROP OPTION OF AlterOrDeleteDependents SHOULD REMOVE ANY ASSOCIATED PERMISSIONS WITH THIS ROLE
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()
Next

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

|||

Thanks for your answer~~

I can delete the role now.But I find that I can skip the step of ArrayList() and delete the role.

I don't know what a risk has in these statement, like following code :

If you know that. Could you tell me,please? Thanks!!

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

' DELETE A ROLES
Dim currentObj As Role

currentObj.database.Roles.FinByName("XYZ")
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

How to delete the role by using AMO(Analysis Management Object)?

I want to delete the role by using AMO, but what I find is only a way that how to create it.

following sample is deleting the members of the role :

Code Snippet

Dim ServerName As Server 'Connect OLAP Server
Dim db As Database 'Database of OLAP Server

Dim role As Role

role = db.Roles.Item(0)
role.Members.Clear()
role.Update()

So I think that deleting the roles of database is like following :

Code Snippet

db.Roles.Clear()

db.Update()

It's wrong. It can't delete roles of a database after executing.

Please tell me what I shall do or where I can find these infoemation, thanks!!

Here's a code sample from one of our developers that does this. (Thanks for the code, Jason.) Keep in mind that in this code, we were looking for Roles that met a naming standard. It's a long story, but you may want to skip that step. Still, I kept it in here (and only changed the naming pattern) so I can insure the code still works with minimal effort. Also, keep in mind this code was written for an SSIS package so you will see some odd ball references in there.

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

Dim roleCollection As New ArrayList()

' FIND ALL OF THE ROLES IN THE ANALYSIS SERVICES DB THAT MATCH THE NAMING PATTERN
For Each currentRole As Role In database.Roles
If (currentRole.Name.StartsWith("XYZ")) Then
' BECAUSE YOU CAN'T DELETE AN ITEM IN A COLLECTION, ADD TO THE roleCollection ARRAY LIST
roleCollection.Add(currentRole)
End If
Next

' DELETE ALL ROLES IN THE roleCollection
For Each currentObj As Role In roleCollection

'DROP OPTION OF AlterOrDeleteDependents SHOULD REMOVE ANY ASSOCIATED PERMISSIONS WITH THIS ROLE
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()
Next

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

|||

Thanks for your answer~~

I can delete the role now.But I find that I can skip the step of ArrayList() and delete the role.

I don't know what a risk has in these statement, like following code :

If you know that. Could you tell me,please? Thanks!!

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

' DELETE A ROLES
Dim currentObj As Role

currentObj.database.Roles.FinByName("XYZ")
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

How to delete orphaned maintenance plan jobs in SQL Server 2005

Hi, I have deleted a Maintenance Plan in SQL 2005 without deleting its job
first. Now I can not delete the job. Is there any way I can delete the job?
Thanks in advance for any help!
I was getting "the maintenance plan related to this job doesn't exist" error.
I set up another maintenance plan and ran it. Now the orphaned job got
cleaned up. So I am all set. Thanks so much for your help!
"Tibor Karaszi" wrote:

> What error message do you get?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:36E58102-BCFE-4E3B-8412-8861F915F616@.microsoft.com...
>
>

How to delete orphaned maintenance plan jobs in SQL Server 2005

Hi, I have deleted a Maintenance Plan in SQL 2005 without deleting its job
first. Now I can not delete the job. Is there any way I can delete the job?
Thanks in advance for any help!What error message do you get?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:36E58102-BCFE-4E3B-8412-8861F915F616@.microsoft.com...
> Hi, I have deleted a Maintenance Plan in SQL 2005 without deleting its job
> first. Now I can not delete the job. Is there any way I can delete the job?
> Thanks in advance for any help!
>|||I was getting "the maintenance plan related to this job doesn't exist" error.
I set up another maintenance plan and ran it. Now the orphaned job got
cleaned up. So I am all set. Thanks so much for your help!
"Tibor Karaszi" wrote:
> What error message do you get?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:36E58102-BCFE-4E3B-8412-8861F915F616@.microsoft.com...
> > Hi, I have deleted a Maintenance Plan in SQL 2005 without deleting its job
> > first. Now I can not delete the job. Is there any way I can delete the job?
> > Thanks in advance for any help!
> >
>
>

How to delete orphaned maintenance plan jobs in SQL Server 2005

Hi, I have deleted a Maintenance Plan in SQL 2005 without deleting its job
first. Now I can not delete the job. Is there any way I can delete the job?
Thanks in advance for any help!What error message do you get?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:36E58102-BCFE-4E3B-8412-8861F915F616@.microsoft.com...
> Hi, I have deleted a Maintenance Plan in SQL 2005 without deleting its job
> first. Now I can not delete the job. Is there any way I can delete the job
?
> Thanks in advance for any help!
>

How to delete a table in Visual Studio 2005

I need to delete a database table from my database. I can not figure out how to do it though. I tried deleting individual columns but go an error message "Drop Failed for column 'TPListHistoryId ' (Microsoft.SqlServer.Smo)

If your DB is attached to your project in VS2005 then you can open it up in server explorer and rightclick onthe table and choose delete.

If you are using sqlserver express management studio then try DROP table <tablename>

|||

Do you need to do this from within an ASP page?

The syntax is:

DROP TABLE <tablename>

You can do this directly in the query analyzer if you open up a query window. Or you could do it from within an ASP.net page. In this case, the syntax is:

SqlConnection conn =new SqlConnection(string here>);
SqlCommand cmd =new SqlCommand("DROP TABLE <tablename>", conn);
cmd.ExecuteNonQuery();

Note: If this table is referenced by foreign constraints, then you cannot simply drop this table without disabling the constraints first. To do this, you must be absolutely positive that you know what you're doing, or you'll possibly end up breaking the integrity of your database.

Good luck!

|||

I am working strictly out of SQL Server 2005. Not visual studio. Sorry I made a mistake. So how can I delete the table? Do you know?

|||

Sorry. I meant to say that I am working directly out of SQL Server 2005. So I messed up in my post. I tried dropping the table in SQL Server 2005, but I get the following error message:

Msg 3726, Level 16, State 1, Line 1

Could not drop object 'Location' because it is referenced by a FOREIGN KEY constraint.

|||

You would need to drop the child tables first. What that message is telling you is that you can't drop the table because there is another table that has a foreign key constraint tide to it. This is put in place to help maintain data integrety.

|||

Check out this previous post for a discussion on how you do a cascading delete from the bottom up:

http://forums.asp.net/p/1144446/1854498.aspx