Introduction into handling errors in PowerShell for SQL Server tasks

By: Diana Moldovan | Comments (1) | Related: > PowerShell

Problem

PowerShell is becoming a great tool for managing SQL Server tasks, but like most tasks that are coded there is always the need for error handling to deal with the unknown. PowerShell has several options for handling and capturing error details and in this tip we will explain these options using PowerShell for SQL Server examples.

Solution

Let's see first where the error messages are stored and how they can be accessed.

There are two kinds of errors in PowerShell:

Terminating Errors

These types of errors are errors which if not handled stop the current script or cmdlet, similar to a .NET exception. Keith Hill gives a good example of a terminating error issued by a "throw" keyword:

    Write-Host "Before"
    throw "Oops!"
    Write-Host "After"

The result, shown below, will not contain "After", because the script stopped when the error was "thrown":

    Before
    
    Oops!
    At :line:8 char:6
    + throw  <<<< "Oops!"

Non-Terminating Errors

These are errors which do not halt the current operation. Most of the "out of the box" cmdlets handle the exceptions in their code and if anything goes wrong, by default ($ErrorActionPreference is set to "Continue") will log the error (see below) and the script will continue with the next cmdlet. If you execute the script interactively, you'll see a red error message displayed.

Both the terminating and the non-terminating errors log the error to the $error array, write the error to the error stream and to the output stream (with one exception which I'll discuss below) and update the $? variable to false.

For now I'll use a very simple example involving the Set-Location cmdlet. Please review this tip for information about how to set up PowerShell to work with SQL Server.

Setting the Error Action Preference

$ErrorActionPreference is a preference variable which determines how PowerShell responds to a non-terminating error. By default it is set to "Continue", i.e. PowerShell will display the error message and will continue to execute the next cmdlets. You can see the default values of the automatic variables by running the GetVariable cmdlet. The below code tries to navigate to a database which does not exist:

    Set-Location SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB
    Write-Host -ForegroundColor Green "Done"

The result should look like:

    Set-Location : Cannot find path 'SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB' 
    because it does not exist.
    At line:2 char:13
    + Set-Location <<<<  SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB
    + CategoryInfo          : ObjectNotFound: (SQLSERVER:\SQL\...BASES\MissingDB:String) 
    [Set-Location], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SetLocationCommand
    Done

The error message display format depends on the value of the $ErrorView preference variable. The default value is "NormalView", which generates a more detailed message.

$ErrorActionPreference is a global setting. If you want to change the behavior at the cmdlet level, use the -ErrorAction common parameter.

     Set-Location SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB -ErrorAction Stop
    Write-Host -ForegroundColor Green "Done"

This time the second line won't be executed, therefore the result will not contain the "Done" message. The error will be treated as a "terminating error".

If you set the error action preference to "SilentlyContinue", the error will not be written to the error stream and to the output stream. Hence the error message will not be displayed./p>

Using $? to Evaluate the Last Statement

$? is a boolean variable which contains the execution status of the last operation. If the operation succeeds it is "true", if not it is set to "false". For example, run this piece of code:

    Set-Location SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB -ea SilentlyContinue
    $?
    Write-Host -ForegroundColor Green "Done"
    $?

The results is shown below. Because of the "SilentlyContinue" option the error will not be written to the error stream and to the output stream and hence the result will contain no error message. The result should look like:

    False
    Done
    True

The $error Array

The $error array holds all the errors which occurred in the current session. $error can contain up to $MaximumErrorCount (by default 256) errors. $error[0] contains the most recent error from the current session. $error is a collection of ErrorRecord objects whose properties hold the error details.

    Set-Location SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB -ea SilentlyContinue
    $Error[0] | Get-Member -MemberType Property
    #result
    TypeName: System.Management.Automation.ErrorRecord
    Name                  MemberType Definition                                                                                                                                      
    ----                  ---------- ----------                                                                                                                                      
    CategoryInfo          Property   System.Management.Automation.ErrorCategoryInfo CategoryInfo {get;}                                                                              
    ErrorDetails          Property   System.Management.Automation.ErrorDetails ErrorDetails {get;set;}                                                                               
    Exception             Property   System.Exception Exception {get;}                                                                                                               
    FullyQualifiedErrorId Property   System.String FullyQualifiedErrorId {get;}                                                                                                      
    InvocationInfo        Property   System.Management.Automation.InvocationInfo InvocationInfo {get;}                                                                               
    PipelineIterationInfo Property   System.Collections.ObjectModel.ReadOnlyCollection`1[[System.Int32, 
                                        mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e08...
    TargetObject          Property   System.Object TargetObject {get;}

Let's list a few of the properties and compare to the error message listed by default:

   Set-Location SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB 
    if(!$?) {
  Write-Host "ExceptionMessage:" $error[0].Exception.Message
  Write-Host "Target Object: " $error[0].TargetObject
  Write-Host "Category Info: " $error[0].CategoryInfo
  Write-Host "ErrorID: " $Error[0].FullyQualifiedErrorId
 }
    #result
    #notice that the error message consists of some of the ErrorRecord property values 
    
    Set-Location : Cannot find path 'SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB' 
    because it does not exist.
    At line:17 char:13
    + Set-Location <<<<  SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB
        + CategoryInfo: ObjectNotFound: (SQLSERVER:\SQL\...BASES\MissingDB:String) 
            [Set-Location], ItemNotFoundException
        + FullyQualifiedErrorId: PathNotFound,Microsoft.PowerShell.Commands.SetLocationCommand
     
    ExceptionMessage: Cannot find path 'SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB'
         because it does not exist.
    Target Object:  SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB
    Category Info:  ObjectNotFound: (SQLSERVER:\SQL\...BASES\MissingDB:String) [Set-Location], 
                       ItemNotFoundException
    ErrorID:  PathNotFound,Microsoft.PowerShell.Commands.SetLocationCommand

The -ErrorVariable Common Parameter

PowerShell offers the option to store the errors at the cmdlet level, by using the -ErrorVariable common parameter. The error will be stored in the specified variable. It will be stored also in the $error array. Run the code below to see the error for each cmdlet:

    Set-Location SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB -ErrorVariable err1`
    -ErrorAction SilentlyContinue
    $err1
    #this database exists, but the table does not exist
    Set-Location SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\Test
    Invoke-Sqlcmd -Database Test -Query "SELECT * FROM NonExistentTable" -ErrorVariable err2`
     -ErrorAction SilentlyContinue
    $err2
    #result
    Set-Location : Cannot find path 'SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB' 
    because it does not exist.
    At line:53 char:13
    + Set-Location <<<<  SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES\MissingDB -ErrorVariable err1 -ErrorAction SilentlyContinue
        + CategoryInfo          : ObjectNotFound: (SQLSERVER:\SQL\...BASES\MissingDB:String) [Set-Location], ItemNotFoundException
        + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SetLocationCommand
 
    WARNING: Using provider context. Server = MyServer.
    Invoke-Sqlcmd : Invalid object name 'NonExistentTable'.
    At line:57 char:14
    + Invoke-Sqlcmd <<<<  -Database Test -Query "SELECT * FROM NonExistentTable" -ErrorVariable err2 -ErrorAction SilentlyContinue
        + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
        + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Next Steps

Hopefully this has given you some idea of how you can get more information about how to utilize some of the error handling options in PowerShell.
Also read this article Deep dive error handling error types which offers more details about this subject
And don't forget to review the additional PowerShell tips

About the author

Diana Moldovan is a DBA and data centric applications developer with 6 years of experience covering SQL 2000, SQL 2005 and SQL 2008.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips