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?