cannot enumerate the collection

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Robert Westerlund Robert Westerlund 2 years, 7 months ago.

  • Author
    Posts
  • #14896
    Profile photo of Igor Micev
    Igor Micev
    Participant

    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?

  • #14907
    Profile photo of Robert Westerlund
    Robert Westerlund
    Participant

    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.

  • #14897
    Profile photo of Igor Micev
    Igor Micev
    Participant

    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

You must be logged in to reply to this topic.