Try catch in powershell getting error

Question

I m getting error as below when I execute this powershell with try catch , did anyone have idea on this? I have try few times but still getting error

This is the code:

Try{  
  
  
   
 $InstanceList = "DESKTOP-6U"  
 $filepath = "C:\b10\SQLQuery2.sql"   
  
     
 $InstanceList |  
     ForEach-Object{  
         $databases = invoke-sqlcmd -ServerInstance $_ -Database "master" -Query "select name from sys.databases where name like '%i%'"   
      
         foreach ($database in $databases) {  
             #Execute scripts   
             Invoke-Sqlcmd -ServerInstance $_ -Database $database.name -InputFIle $filepath    
                       
         }  
 }  
Catch   
{  
$ErrorMessage = $_.Exception.Message | Out-File C:\b10\DBA_ConfigChanges.txt -append  
}   
  
 }  

Answer 1

You should take more care about proper code formating, then you would see it. The closing bracket in line 24 is at the wrong place, should be more line 18+

Try  
    {  
    $InstanceList = "DESKTOP-6U"  
    $filepath = "C:\b10\SQLQuery2.sql"   
         
    $InstanceList |  
        ForEach-Object  
        {  
            $databases = invoke-sqlcmd -ServerInstance $_ -Database "master" -Query "select name from sys.databases where name like '%i%'"           
            foreach ($database in $databases)   
            {  
              #Execute scripts   
              Invoke-Sqlcmd -ServerInstance $_ -Database $database.name -InputFIle $filepath                             
            }  
        }  
    }  
 Catch   
 {  
    $ErrorMessage = $_.Exception.Message | Out-File C:\b10\DBA_ConfigChanges.txt -append  
 }  

Answer 2

The error message clearly says "error at line 24" the closing bracket is wrong and the hint "TRY- Block is missing his Catch-Block". So everything is there to investigate everything... if you take care of formatting and reading carefully the error-message next time, you won't need to post it here (even if we like to help) you can find any error by yourself ;-)

As Olaf stated, you should take better care of your format:

Try {  
    $InstanceList = "."  
    $filepath = "C:\b10\SQLQuery2.sql"   
              
    $InstanceList | ForEach-Object{  
        $databases = invoke-sqlcmd -ServerInstance $_ -Database "master" -Query "select name from sys.databases where name like '%i%'"   
        foreach ($database in $databases) {  
            #Execute scripts   
            Invoke-Sqlcmd -ServerInstance $_ -Database $database.name -InputFIle $filepath    
        }  
    }  
}  
Catch {  
    $ErrorMessage = $_.Exception.Message | Out-File C:\b10\DBA_ConfigChanges.txt -append  
}  

Answer 3

While using a more "intellegent" editor will help correct those "missing brackets" and sundry other syntax problems before you run the code, there might be a more significant problem in that code.

I'm not familiar with the behavior of the Invoke-SQLCmd cmdlet, but it does have the ability to use the "-ErrorAction STOP" that will cause non-terminating errors to be treated as terminating. Without that, the "Try/Catch" won't do anything for non-terminating errors. I'm assuming here that an error detected at the SQL server side won't cause the Invoke-SQLCmd to throw an exception -- which means that the data returned by the cmdlet would be something other than database names. The cmdlet may return an error object instead -- or it may return the error in the error stream and that won't be detected because that stream hasn't been redirected to the success stream.

The net result is that the code may produce no output at all if the $databases variable contains a $null value.