Author Posts

April 30, 2014 at 1:47 am

Hi,

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: (:) [], ExtendedTypeSystemException
+ FullyQualifiedErrorId : ExceptionInGetEnumerator

#Sript
$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]$proc.name + ".sql");
# New procedure definition sql
$proc.Script() -replace($matchtext, $replaceText) | Out-File ($changeFolder + "" + [string]$proc.name + ".sql");
# If set to true this will change the procedure definition on the server!
if($alter)
{
$proc.TextBody = $proc.TextBody -replace($matchtext, $replaceText);
$proc.Alter();
Write-Host "Altered " $proc.Name;
}
}
}

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

Can you help on this issue?

April 30, 2014 at 4:37 am

Hi

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...

BR,
Igor

April 30, 2014 at 11:51 am

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.