How to catch only SQL timeout errors in PowerShell?

Question

I am trying to capture only timeout errors in a script for PowerShell 5.1. The error prints out that the execution timed out, but when I print out the error name it comes back with [Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException]. This catches the timeout error, but also catches other errors. Is there a way to specify only timeout errors?

try
{
    $results1 = Invoke-Sqlcmd -Query $query -ConnectionString $connectionString
}
Catch [Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException] 
{
    Write-Output "Timeout error. Try again"
}

Error without catch

Invoke-Sqlcmd : Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

If the errors all return the same error type, I don't know how I would get Catch to recognize that. Might be able to split them up inside the catch using the extended error information and string parsing, but that is pretty clunky. I'm interested to see if there is a better way. — Matthew, Feb 06 '20 at 21:14
Explore your $Error[0] variable for something specific for which you can evaluate: $Error[0].CategoryInfo.Category, $Error[0].CategoryInfo.Reason, etc. Use Get-Member with $Error[0] to help explore. You'll find something unique. Once you do, put an If statement inside your Catch block with a condition that makes sense. — tommymaynard, Feb 06 '20 at 22:08
Try adding `-ErrorAction Stop` to make the error a terminating error. Moreover, add another `Catch {}` block (_do not specify an error type_) to handle any other terminating errors that occur. — JosefZ, Feb 06 '20 at 23:11

score 0 · Answer 1 · answered Feb 06 '20 at 22:12

Here's an example of my above comment that may prove helpful.

try {
    [System.Void](New-Item -Path $LogPath -ItemType Directory -ErrorAction Stop)
} catch {
    If ($Error[0].CategoryInfo.Category -eq 'PermissionDenied' -or
        $Error[0].CategoryInfo.Reason -eq 'UnauthorizedAccessException') {
        Write-Output -InputObject "Please check that you have access to create a directory."
    } Else {
        Write-Output -InputObject "An error has occurred: $($Error[0].Exception.Message)."
    } # End If-Else.
} # End try-catch.

Lance U. Matthews · Answer 2 · 2020-02-06T23:47:22.443

I was not able to find any documentation for the [SqlPowerShellSqlExecutionException] class, however, by downloading and inspecting the SqlServer module (specifically, the Microsoft.SqlServer.Management.PSSnapins assembly) I was able to find that exception class derives directly from [Exception] (so there is not a general SQL exception type you could catch instead) and contains exactly one property named SqlError and of type [SqlError].

Looking at that documentation there is, unfortunately, not a nice enumeration property describing the various potential errors, however I do see there is a Number property. I'm a little confused what the last column of the table on that page is indicating, but it seems like if you write your catch block like this...

Catch [Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException] 
{
    # Error number specified at https://learn.microsoft.com/dotnet/api/system.data.sqlclient.sqlerror.number#remarks
    if ($_.Exception.SqlError.Number -eq -2)
    {
        Write-Output "Timeout error. Try again"
    }
}

...or like this...

Catch [Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException] 
{
    # Error number specified at https://learn.microsoft.com/dotnet/api/system.data.sqlclient.sqlerror.number#remarks
    if ($_.Exception.SqlError.Number -eq 258)
    {
        Write-Output "Timeout error. Try again"
    }
}

...that will allow you to specifically target timeout errors. Or perhaps you need to check for both of those error numbers. See Database Engine Events and Errors for a lengthy list of error numbers to match against, and also consider writing your catch block like this to inspect what Number you're getting for a timeout error...

Catch [Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException] 
{
    $_.Exception.SqlError | Select-Object -Property 'Number', 'Message'
}

There is also an SqlError.Message property, so as a last resort you could always do something like this...

Catch [Microsoft.SqlServer.Management.PowerShell.SqlPowerShellSqlExecutionException] 
{
    #TODO: Is $_.Exception.SqlError.Message...
    #    - ...the same string as $_.Exception.Message ?
    #    - ...a substring of     $_.Exception.Message ?
    #    - ...more specific than $_.Exception.Message ?
    if ($_.Exception.SqlError.Message -eq 'Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.')
    {
        Write-Output "Timeout error. Try again"
    }
}

By the way, though Write-Output might happen to achieve the goal of writing text to the console, if your intent is to provide feedback to the user then Write-Host, Write-Information or Write-Warning would be more suitable cmdlets for that. See PowerShell difference between Write-Host and Write-Output?.

How to catch only SQL timeout errors in PowerShell?

2 Answers2