Powershell Error Handling

Published 16 January 11 10:56 PM | MartinBell

I remember giving a talk at Developer Day Scotland on T-SQL and one of the feedback was something like “A well presented talk on what is usually a very boring subject”. Although error is a mandatory part of programming, and some people may find testing all the possible use cases tedious, I would not say error handling itself is boring. I remember working on a system where that used a standard error handling template, and when the programmers created a new procedure they would copy and paste the code from an existing procedure. Unfortunately template was floored and it was feasible for errors to go undetected.

In Powershell there are three methods of error handling:

    1. using a Trap handler
    2. using Try/Catch/Finally
    3. testing a known condition


The trap handler has similarities to using ON ERROR in Visual Basic but it is active for a given scope. It is a available in Powershell 1.0 and 2.0. Try/Catch is available in Powershell 2.0 and will catch errors generated in the Try block. Testing a known condition is often overlooked, but if you are programming defensively it should also be used in conjunction with the other two methods.

If you want to know more about error handling check out Steven Murawski’s blog post this also gives information about the error variables that are populated with exception information.

The following examples require the following assemblies loaded:

# load assemblies
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

An example of using trap:

function fn_getdbs ( [string] $server, [string] $userName, [string] $password ) {
    trap {
        write-host "Message: " $_.Exception.Message ;
        return ;
    } ;

    $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection ;
    $conn.LoginSecure = $false ;
    $conn.Login = $userName ;
    $conn.Password = $password ;
    $conn.ServerInstance = $server ;
    $conn.NonPooledConnection = $true ; 

    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server ($conn) ;
    foreach($j in $srv.Databases) {
        write-host $j.name ;
    } ;
}

To invoke the trap use the command like:

fn_getdbs “localhost” “validuser” “invalidpassword”

and the trap will output the error message:

Message:  Failed to connect to server localhost.

You can trap specific exceptions. For example: trapping the ConnectionFailureException error to output your own message to avoid giving unwanted details to the user:

function fn_getdbs ( [string] $server, [string] $userName, [string] $password ) {
    trap [Microsoft.SqlServer.Management.Common.ConnectionFailureException] {
        write-host "Connection Failed!!!"
        return ;
    } ;
    trap {
    write-host "Message: " $_.Exception.Message ;
    return ; 
    } ;

    $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection ;
    $conn.LoginSecure = $false ;
    $conn.Login = $userName ;
    $conn.Password = $password ;
    $conn.ServerInstance = $server ;
    $conn.NonPooledConnection = $true ;

    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server ($conn) ;
    foreach($j in $srv.Databases) {
        write-host $j.name ;
    } ;
}

Calling the function to generate a connection failure e.g

fn_getdbs “localhost” “validuser” “invalidpassword”

will generate the message:

Connection Failed!!!

but calling the function with no username ie.

fn_getdbs "localhost" "" "validpassword"

will generate an error:

Message:  Exception setting "Login": "Cannot apply value 'null' to property Login: Value cannot be null.."

An example of using try/catch:

function fn_getdbs ( [string] $server, [string] $userName, [string] $password ) { 
    try {
        $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection ;
        $conn.LoginSecure = $false ;
        $conn.Login = $userName ;
        $conn.Password = $password ;
        $conn.ServerInstance = $server ;
        $conn.NonPooledConnection = $true ;
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server ($conn) ;
        foreach($j in $srv.Databases) {
            write-host $j.name ;
        } ;
    }
    catch {
        $err = $Error[0].Exception ;
        write-host "Error caught: "  $err.Message ;
        continue ;
    } ;
}

Calling the function to generate a connection failure e.g

fn_getdbs “localhost” “validuser” “invalidpassword”

will generate the message:

Error caught:  The following exception was thrown when trying to enumerate the collection: "Failed to connect to server localhost.".

Like using trap you can catch specific exceptions and handle them appropriately:

function fn_getdbs ( [string] $server, [string] $userName, [string] $password ) { 
    try {
        $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection ;
        $conn.LoginSecure = $false ;
        $conn.Login = $userName ;
        $conn.Password = $password ;
        $conn.ServerInstance = $server ;
        $conn.NonPooledConnection = $true ;
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server ($conn) ;
        foreach($j in $srv.Databases) {
            write-host $j.name ;
        } ;
    }
    catch [Microsoft.SqlServer.Management.Common.ConnectionFailureException] {
        write-host "Connection Failed!!!"
        continue ;
    }
    catch {
        $err = $Error[0].Exception ;
        write-host "Error caught: " $err.Message ;
        continue ;
    } ;
}

Calling the function to generate a connection failure e.g.

fn_getdbs “localhost” “validuser” “invalidpassword”

will generate the message:

Connection Failed!!!

but calling the function with no username ie.

fn_getdbs "localhost" "" "validpassword"

will generate an error:

Error caught:  Exception setting "Login": "Cannot apply value 'null' to property Login: Value cannot be null.."


You may have noticed the error message in the previous example:

Error caught:  The following exception was thrown when trying to enumerate the collection: "Failed to connect to server localhost.".

mentions enumerating collections. This is because the error that is trapped is not generated from the line:

        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server ($conn) ;

but by the line:

        foreach($j in $srv.Databases) {

this is when the connection to the database is actually attempted. In addition SMO does not always generate exceptions for login failures. This is where testing a known condition may be useful. The following example will demonstrate this:

function fn_getjobs ( [string] $server, [string] $userName, [string] $password ) { 
    try {
        $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection ;
        $conn.LoginSecure = $false ;
        $conn.Login = $userName ;
        $conn.Password = $password ;
        $conn.ServerInstance = $server ;
        $conn.NonPooledConnection = $true ;
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server ($conn) ;
        if ( $srv.Jobserver -ne $null ) {
            foreach($j in $srv.Jobserver.Jobs)            {
                write-host $j.name ;
            }
        } else {
            Throw "No Server! Could be a connection problem???" ;
        }
    }
    catch {
        $err = $Error[0].Exception ;
        "Error caught: " + $err.Message ;
        continue ;
    } ;
}

The connection is attempted on the line:

        if ( $srv.Jobserver -ne $null ) {

and if the connection fails the value of Jobserver will be null, if the login is successful $srv.Jobserver will have a value. This also shows the use of the Throw statement to generate an exception, so if the login fails you will get the message:

Error caught: No Server! Could be a connection problem???

I find this all quite interesting and not at all boring!

Comments

# Martin Bell UK SQL Server MVP said on January 17, 2011 09:34 AM:

SMO error handling is not consistent when it comes to login failures. This is not a good for a programming having to handle errors as patterns can't necessarily be used .

# Dew Drop – January 17, 2011 | Alvin Ashcraft's Morning Dew said on January 17, 2011 01:30 PM:

Pingback from  Dew Drop – January 17, 2011 | Alvin Ashcraft's Morning Dew

# http://vp164067.hk.uac65.hknet.com/ said on December 6, 2014 07:46 AM:

Powershell Error Handling - Martin Bell UK SQL Server MVP

This Blog

SQL Blogs

Syndication