This is a .NET collection issue: You cannot change the contents whilst enumerating over it i.e. you must not add to or remove items from a collection you are using foreach to loop around.

NOTE: For those of you who know that it is using IEnumerable (or returns another enumerable compatible enumerator) then you know it can be more than foreach that can cause this, for those of you who don't feel free to look into enumeration as an additional exercise.

You will need to replace:

foreach ($proc in $procs)

{

#Assuming that all non-system stored procs have proper naming convention and don't use prefixes like "sp_"

if ( $proc.Name.IndexOf("<any condition>") -eq 0)

{

$fileName = $proc.name.Replace("\", "")

"Scripting SP $fileName"

$scriptfile = "$rootDrive\$filename.sql"

$MyScripter.Options.FileName = $scriptfile

#AppendTofile has to be 'true' in order that all the procs' scripts will be appended at the end

$MyScripter.Options.AppendToFile = "False"

$MyScripter.Script($proc)|out-null

$proc.Drop()

}

}

with something like this:

# Create an array for the stored procedures to drop.

$storedProceduresToDrop = @()

foreach ($proc in $procs)

{

#Assuming that all non-system stored procs have proper naming convention and don't use prefixes like "sp_"

if ($proc.Name.IndexOf("<any condition>") -eq 0)

{

$fileName = $proc.name.Replace("\", "")

"Scripting SP $fileName"

$scriptfile = "$rootDrive\$filename.sql"

$MyScripter.Options.FileName = $scriptfile

#AppendTofile has to be 'true' in order that all the procs' scripts will be appended at the end

$MyScripter.Options.AppendToFile = "False"

$MyScripter.Script($proc)|out-null

$storedProceduresToDrop = $storedProceduresToDrop + $proc

}

}

# Drop the stored procedures.

$currentIndex = 0

while ($currentIndex -lt $storedProceduresToDrop.Count)

{

$storedProceduresToDrop[$currentIndex].Drop()

$currentIndex = $currentIndex + 1

}

I haven't had the chance to test the code so please do test it with a copy of a database or by just writing to the host etc.

I hope this helps.

(Edited to set code to plain code block due to other code block assuming backslash was escaping a quotation mark.)