Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Powershell Script help for Scripting


Powershell Script help for Scripting

Author
Message
RatanDeep Saha
RatanDeep Saha
SSChasing Mays
SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)SSChasing Mays (648 reputation)

Group: General Forum Members
Points: 648 Visits: 685
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()




}
}
}
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8252 Visits: 6123
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!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search