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.SetLocationCommandWARNING: 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.
- And don’t forget to review the additional PowerShell tips
