Error Detection from Powershell Invoke-Sqlcmd Not Always Working

Error detection from Powershell Invoke-Sqlcmd not always working?

Regardless of the ErrorAction setting, invoke-sqlcmd cmdlet has a bug present in SQL Server 2008, 2008 R2 and 2012 versions of the cmdlet where T-SQL errors like divide by 0 do not cause an error. I logged a connect item on this and you can see details here:

https://connect.microsoft.com/SQLServer/feedback/details/779320/invoke-sqlcmd-does-not-return-t-sql-errors

Note: the issue is fixed in SQL 2014, however it does not appear a fix has been or will be provided for previous versions.

powershell try-catch doesn't capture invoke-sqlcmd error

So I'm an idiot and I was copying from an outdated copy. My NEW version that reflects the changes made per the link in my question works perfectly.

Catching Errors from Invoke-SqlCmd

What you want to do as tell the cmdlet Invoke-Sqlcmd to stop if an error is received, then it will hit your catch block properly. You do this -ErrorAction Stop. So using the command in this manner will have the catch block entered

$fullServerName = 'Doesnotexist'
$loginCommand = "SELECT @@SERVERNAME"
Try # Create Login
{
    # Importing sqlserver module to run the commands
    if (-not (Get-Module -Name "sqlserver"))
    {
        Import-Module sqlserver
    }
    Write-Host "Checking server $fullServerName to see if login [Example_Login] exists. Will create login if it does not already exist."
    Invoke-Sqlcmd -ServerInstance $fullServerName -Query "$loginCommand" -ErrorAction Stop

    Write-Host "Login command successfully executed.`n"
}
Catch
{
    Write-Host "An error has occurred while attempting to add login [Example_Login] to server $fullServerName."
    Write-Host $_
}

An example of the above executed via a ps1 file:

Then if I remove the -ErrorAction Stop from the script:

Catching permission errors with invoke-sqlcmd and powershell

As far as I know, in order for the catch to be executed, you would need to set your -ErrorAction to Stop.

I suppose what you could do is:
set: $ErrorActionPreference = "Continue" at the top of your cmdlet and then have -ErrorAction stop on the call to invoke-sql, this should cause it to drop into the catch but continue to behave with the continue behaviour in the rest of your script.

Another option if you want to leave -ErrorAction continue would be to also set -ErrorVariable invokeSqlError and then check if that variable contains a value. If it does an error has occurred.

Something like:

if ($invokeSqlError){
    write-output "Error occured $invokeSqlError"
}

---

Related Topics