Try Catch with Invoke-SQLCmd and -ErrorAction SilentlyContinue

Question

Basically, I want to be able to catch the warnings returned from Invoke-SqlCmd without having to stop the script running. The following code does not work:

TRY {
    Invoke-SQLCMD -Query "selects * from syscomments" -ServerInstance $ServerAddress -Database $DatabaseName -ErrorAction 'SilentlyContinue' 
}
CATCH {
    Write-Host "[!] Errors returned, check log file for details" -ForegroundColor RED
    $_ | Out-File -Append "path to log"
}

This just suppresses all output and doesn't catch the error. Changing the error type to Stop does catch the error but I need these scripts to continue running after an error is hit.

Answer 1

You can use a trap statement:

trap { # invoked on terminating errors
  Write-Host "[!] Errors returned, check log file for details" -ForegroundColor RED
  $_ | Out-File -Append "path to log" 
  continue # continue execution
}

# Elevate all non-terminating errors to (script-)terminating errors.
$ErrorActionPreference = 'Stop'

# All errors - whether non-terminating or terminating - now trigger
# the trap, which, due to use of `continue`, continues execution after each error.

Invoke-SQLCMD -Query "selects * from syscomments" -ServerInstance $ServerAddress -Database $DatabaseName