November 18, 2013 at 10:26 am
Hello ,
Please assists me if any one has idead about this . I have script in powershell which script out SP with specific condition . Want to achieve is drop those SP once the script generation is completed . My script works only for one SP at time and I want it loop properly .
if I add $proc.drop() and throws error :
An error occurred while enumerating through a collection: Collection was modified; enumeration operation may not execut
e..
At E:\RD\LVer.ps1:18 char:40
+ foreach <<<< ($proc in $procs)
+ CategoryInfo : InvalidOperation: (Microsoft.SqlSe...dListEnumerator:SmoSortedListEnumerator) [], Runtim
eException
+ FullyQualifiedErrorId : BadEnumeration
[u]Actual Script :[/u]
$rootDrive = "<Location for scripts>"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "<ServerName>"
$dbName = $srv.databases["<Database Name>"]
$procs = $dbName.StoredProcedures
$MyScripter.Server=$srv
#STORED PROCEDURES
if($procs -ne $null)
{
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()
}
}
}
November 19, 2013 at 2:44 am
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.)
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply