February 19, 2008 at 10:10 am
I am having issues with the following code within a SSIS package. It is designed to drop all database users before a restore operation commences and following a kill all connections script. The issue I am running into is that at run-time this code generates an error saying the collection has changed after the first user is dropped which causes the script to exit. Anyone have any ideas what is wrong here? Of note, this is running against a SQL 2000 database not a SQL 2005 database.
Any help you can provide would be greatly appreciated.
Scott
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
' References Used, includes defaults:
' Microsoft.SqlServer.ConnectionInfo
' Microsoft.SqlServer.ManagedDTS
' Microsoft.SqlServer.ScriptTask
' Microsoft.SqlServer.Smo
' Microsoft.Vsa
' System
' System.Data
' System.Windows.Forms
'
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Smo
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Try
Dim srvr As Server = New Server(Dts.Variables("DestinationServer").Value.ToString)
Dim db As Database
db = srvr.Databases(Dts.Variables("DestinationDB").Value.ToString)
Dim usr As User
For Each usr In db.Users
' Check to see if the user is a system object, if not then drop the user
If Not usr.IsSystemObject Then
usr.Drop()
End If
Next
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
End Class
February 19, 2008 at 11:37 am
Replacing the For Each loop with a For Next loop should work.
Dim usr as User
For x As Integer = db.Users.Count - 1 to 0 Step -1
usr = db.Users(x)
If Not usr.IsSystemObject Then
usr.Drop()
End If
Next x
February 19, 2008 at 4:17 pm
Thanks Todd! I will try this tomorrow when I put my dev hat back on 🙂
Scott
February 7, 2011 at 9:23 am
Any chance you know how to pull SQL Auth from a connection manager and use it with the server object?
Thanks
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply