Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Options For Reporting Errors In Catch Statements Using PowerShell


By:   |   Last Updated: 2018-12-27   |   Comments   |   Related Tips: More > PowerShell

Problem

We use several third party tools that use PowerShell functions, along with other languages for running functions along with testing functions. These scripts can involve connections to databases, running unit tests, validating data, among other functionality. We want our execution of these scripts to provide us with an option that we select to fail, caution us with what the failure was, or pass even if the script fails.

Solution

When we run scripts, we'll generally want to report a failure with an exit. However, there are situations where a failure in the script may be expected, or we may be testing and want the next step to occur, even if there's a failure. Building an override can be useful and as we'll see, we can create a re-usable function for this that we can strategically place in situations that call for its use.

Whether we use custom software we develop or we use a tool that reads a script's output, we want to consider how the output from a script is read. As we'll see in this tip, we have a few options for how we can handle errors - fail, caution, pass while reporting that it actually failed, or do a combination of the latter two while ending in failure after passing other tests. The below are examples where we may want to use one of these all involving the same scenario, but different contexts - an ETL job that extracts data from a source:

  • Fail. When the ETL job attempts to connect to the source and it fails, the script reports a failure.
  • Caution. When the ETL job attempts to connect to the source and passes, but the data are not available, so it cautions that data are not present, thus it will try again.
  • Pass reporting a failure during a run. We're testing the ETL job and we want to see if all our steps function, even if we don't have some of the pieces in place, so we pass even after a failure to see if other steps execute correctly. Another context of this would be if sometimes a data source isn't supposed to be online (ie: expected random behavior) and we pass a script that fails a connection because it simply means that the day meant there was no data. In other words, a "failed connection" was possible.
  • Pass even after a failure, but end in failure. Similar to the first example in the previous scenario, except that we eventually want the script to fail at the end if one failure (or more) was experienced after validating all other steps.
Since I find demarcating steps to their own jobs, tasks or individual script runs, I rarely use the fourth scenario. However, some environments do many things in one job or script, so it's worth considering for debugging.

Using PowerShell ISE, we can compare the output of the following three functions - Write-Output, Write-Warning and Write-Error. In this tip, we'll use these three functions in our call, though we can use Write-Output for these and pass in different parameters.

$message = "This is our returned message."
Write-Output $message
Write-Warning $message
Write-Error $message
Image

For re-use, we'll create a function that we can call in the catch block of other functions. We'll take two parameters - a message parameter that accepts a string and a set parameter that will determine the result output of the function. When we want the function to fail as we see in the above example, the function will abruptly stop, whereas a caution and pass will continue. For this function, the result possibilities will be fail (terminate script), caution (continue script with an alert) and pass (continue script) and for this parameter we'll use the validate set accepting either fail, caution or pass. Since both of these parameters are required, we'll set the mandatory for both of the parameters to be true.

Function Catch-Options {
    Param(
        [Parameter(Mandatory=$true)][string]$message
        , [Parameter(Mandatory=$true)][ValidateSet("Fail","Caution","Pass")]$outputoption
    )
    Process
    {
        switch ($outputoption)
        {
            "Fail" { Write-Error $message }
            "Caution" { Write-Warning $message }
            "Pass" { Write-Output $message }
        }
    }
}

With our function, we will now call our function inside other functions and test this. In the below script, we execute 2 functions that each have our created function inside of them and intentionally set them both to fail - in the first script, we don't enter a server name and in the second script we don't pass in a file that exists to read. We see how both of these functions will report cautions and passes even though they failed. Since a failure would exit the script and we see the failure is set to default in both scripts, we don't test this in the when calling these scripts.

Function Catch-Options {
    Param(
        [Parameter(Mandatory=$true)][string]$message
        , [Parameter(Mandatory=$true)][ValidateSet("Fail","Caution","Pass")]$outputoption
    )
    Process
    {
        switch ($outputoption)
        {
            "Fail" { Write-Error $message }
            "Caution" { Write-Warning $message }
            "Pass" { Write-Output $message }
        }
    }
}

Function Execute-Sql {
    Param(
        [Parameter(Mandatory=$true)][string]$command
        , [Parameter(Mandatory=$false)][ValidateSet("Fail","Caution","Pass")]$outputoption = "Fail"
    )
    Process
    {
        $scon = New-Object System.Data.SqlClient.SqlConnection
        $scon.ConnectionString = "Data Source=;Initial Catalog=master;Integrated Security=true"
        
        $cmd = New-Object System.Data.SqlClient.SqlCommand
        $cmd.Connection = $scon
        $cmd.CommandTimeout = 0
        $cmd.CommandText = $command

        try
        {
            $scon.Open()
            $cmd.ExecuteNonQuery()
        }
        catch [Exception]
        {
            Catch-Options -message (("(" + $MyInvocation.MyCommand.Name).ToString() + ") " + $_.Exception.Message) -outputoption $outputoption
        }
        finally
        {
            $scon.Dispose()
            $cmd.Dispose()
        }
    }
}

Function Read-FullFile {
    Param(
        [Parameter(Mandatory=$true)][string]$file
        , [Parameter(Mandatory=$false)][ValidateSet("Fail","Caution","Pass")]$outputoption = "Fail"
    )
    Process
    {
        try
        {
            $readfile = New-Object System.IO.StreamReader($file)
            $readfile.ReadToEnd()
        }
        catch [Exception]
        {
            Catch-Options -message (("(" + $MyInvocation.MyCommand.Name).ToString() + ") " + $_.Exception.Message) -outputoption $outputoption
        }
        finally
        {
            if ($readfile -ne $null)
            {
                $readfile.Dispose()
            }
        }
        
    }
}

Execute-Sql -command "EXEC stp_OurProcedure" -outputoption Caution
Execute-Sql -command "EXEC stp_OurProcedure" -outputoption Pass

Read-FullFile -file "C:\InvalidFile.txt" -outputoption Caution
Read-FullFile -file "C:\InvalidFile.txt" -outputoption Pass
Image

We can also see a few key details in both of the scripts that we intentionally passed invalid parameters. Our try-catch statements are designed to catch when execution occurs, but it's possible that I pass in an invalid parameter to the connection string that will throw a failure since setting the connection string isn't in a try-catch block. We also see something similar with our Read-FullFile script with the dispose: note that we only call dispose if the object of $readfile exists (meaning the object is not null). If the object failed creation, it would throw an error when trying to dispose a non-existent object.

As a reminder, if we have methods or are executing actions outside a try-catch block, this function won't assist us with handling failures, cautions and passes. We must wrap all executions in try-catch statements so that we can control the output. As an example of this, suppose we have a tool that runs PowerShell scripts as long as a script hasn't thrown an error - the tool would terminate on the first attempt to divided by zero and never reach the second attempt, even if the second attempt wouldn't throw an error. For this, we want all method calls and executions to be within a try-catch.

Write-Host (11/0).ToString()

try 
{
    Write-Host (1/0).ToString()
}
catch [Exception]
{
    Write-Host $_.Exception.Message
}
Next Steps
  • A script context where a failure should abort a script and raise concerns should always use the failure approach.
  • The caution and pass can be useful when a failure might be expected behavior and there may be steps we still want executed, while retaining the results of what happened in the attempt that failed.
  • In testing scripts, the function we created can be useful to pass over failures during testing. Once we've validated this, we can then revert the calls to fail.
  • In some contexts, reporting errors with detailed information should be avoided due to security risks associated with errors. While we may find it appropriate to use this function in testing and validation, this should be removed in higher environments with strict security requirements. Errors provide information that can be used inappropriately, so we should consider this in contexts where that inappropriate information may invite risks and costs.


Last Updated: 2018-12-27


next webcast button


next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools