cannot enumerate the collection


I run the following script on my office pc (in domain) and it ends up with error message

The following exception occurred while trying to enumerate the collection: “An exception occurred while executing a
Transact-SQL statement or batch.”.
At line:1 char:18

  • foreach($proc in $db.StoredProcedures)
  • CategoryInfo : NotSpecified: (:slight_smile: [], ExtendedTypeSystemException
  • FullyQualifiedErrorId : ExceptionInGetEnumerator

$server = “skl-spare-prese\Sql2014”; # The SQL Server instance name
$database = “AdventureWorks2012”; # The database name
$matchText = “CREATE”; # Definition text to search .Be aware this accepts a regular expression
$replaceText = “ALTER”; # Text to replace $matchText
$alter = $false; # Set to true if you want the script to alter database objects
$backupFolder = “D:\temp\backup\AdventureWorks2012\Create”; # Change script folders. Need a \ (back slash) on the end
$changeFolder = “D:\temp\change\AdventureWorks2012\Alter” # One file per object, backup & change folders

Load the SQL Management Objects assembly (Pipe out-null supresses output)

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null

Create our SMO objects

#$srv = New-Object “Microsoft.SqlServer.Management.SMO.Server” $server;
$srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $server
$db = New-Object (“Microsoft.SqlServer.Management.SMO.Database”);

Get the database

$db = $srv.Databases[$database];

For each stored procedure in the database

foreach($proc in $db.StoredProcedures)

For each matching stored prcoedure

if($proc.TextBody -match $matchText)
Write-Host "Processing proc: " $proc.Name;

Backup of the original proc definition

$proc.Script() | Out-File ($backupFolder + “” + [string]$ + “.sql”);

New procedure definition sql

$proc.Script() -replace($matchtext, $replaceText) | Out-File ($changeFolder + “” + [string]$ + “.sql”);

If set to true this will change the procedure definition on the server!

$proc.TextBody = $proc.TextBody -replace($matchtext, $replaceText);
Write-Host "Altered " $proc.Name;

When I run it on my private pc it runs successfully.

Can you help on this issue?


I found out that the reason so far is the sql server version, i.e. I tried it for sql server 2014 (evaluation), and run it on sql 2012 and sql 2008 works well.
Please leave your comments how it gets in use for sql 2014…


Could you look into the InnerException property on the exception object (and potentially the InnerException of that exception, etc)? That might hold more information on what was the actual problem.