Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Powershell Script help for Scripting Expand / Collapse
Author
Message
Posted Monday, November 18, 2013 10:26 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, December 1, 2014 11:39 AM
Points: 611, Visits: 450
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()




}
}
}






Post #1515301
Posted Tuesday, November 19, 2013 2:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 5:52 PM
Points: 5,831, Visits: 3,751
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!!!
Post #1515462
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse