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

Free SQL Server Webcast > Building Really Fast SQL Server VMs
 

Error handling with try-catch-finally in PowerShell for SQL Server


By:   |   Read Comments (3)   |   Related Tips: More > PowerShell

Problem

PowerShell V2 introduces the "try-catch-finally" statements, similar to those you already use when you write .NET code. "Try-catch-finally" encloses a block of script that is likely to produce errors under specific circumstances and therefore helps you to keep your code organized. Below you'll find a short usage guide for this error handling construct. This was done using Windows 8.1 Pro x64 / PowerShell v4 / SQL Server 2012 SP1 environment.

Solution

Open a new PowerShell session and make sure that the SQLPS module is not imported. If you run Get-Module, SQLPS should not be in the result list. For example, if you use the Windows PowerShell ISE:

Get-Module
GET-Module when SQPS is not loaded

Compare this result with the one you obtain after importing SQLPS:

Import-Module SQLPS -DisableNameChecking
Get-Module

GET-Module when SQPS is loaded

Now when we run this piece of code - remember that SQLPS is not loaded.

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

The result should look like:

Non-terminating error

Since SQLPS is not loaded, PowerShell returns a "drive not found" exception. Notice that this error is a non-terminating error and the "Done" message will be part of the result.

You won't be able to reproduce this situation when running PowerShell from within SQL Server Management Studio, because the SQL Server specific cmdlets and provider are already loaded on start. Right click, for example, on the Server node in the object view pane, click on "Start PowerShell" and run Get-Module.

Powershell for SQL Server

Now let's enclose the above error prone line of code in a "try-catch" as follows:

try{
    Set-Location SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES 
    Write-Host -ForegroundColor Green "Done"
}
catch{
    Write-Host -ForegroundColor DarkYellow "You're WRONG"
    Write-Host -ForegroundColor Magenta $Error[0].Exception
}
finally{
    Write-Host -ForegroundColor Cyan "It's finally over..."
}
        

As you can see, the result is similar to what you've obtained before, except the last line which comes from the "finally" line of code. The "catch" code is not executed.

Try-catch with a non-terminating error

For a non-terminating error, adding a "try-catch" construct has no effect unless you add an "error action" parameter or preference set to "stop", forcing it to become a terminating error as shown below:

try{
    Set-Location SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES -ErrorAction Stop
    Write-Host -ForegroundColor Green "Done"
}
catch{
    Write-Host -ForegroundColor DarkYellow "You're WRONG"
    Write-Host -ForegroundColor Magenta $Error[0].Exception
}
finally{
    Write-Host -ForegroundColor Cyan "It's finally over..."
}
        

Try-catch with a terminating error

When it encounters the terminating error, PowerShell writes the error to the $Error array and transfers the execution to the "catch" block, if any. The "catch" block contains the error-handling code. If there is no "catch" block, PowerShell simply writes the error to the error stream. The "finally" block is optional. It will execute after both the "try" and "catch" have completed, regardless of the occurrence of any error. The non-terminating error example result contains the "It's finally over" message even if the execution was not transferred to the "catch" block. Use "finally" to perform clean-up tasks such as deleting temporary output files you no more need. You can't use a "try" block alone; you need one "catch" block or one "finally" block to run the code.

It is a good practice to write several "catch" blocks to catch specific exceptions. You should place the most specific blocks first, and end with a "catch all" block. PowerShell will search the "catch" blocks from the top to the bottom and stops when it finds a match. Use $Error[0].Exception.GetType() to find the type of the exception you are dealing with. In the following example the exception type is written in bright yellow. After the "catch" code, PowerShell will execute the "finally" block and will display the light blue message.

try{
    Set-Location SQLSERVER:\SQL\MyServer\DEFAULT\DATABASES -ErrorAction Stop
    Write-Host -ForegroundColor Green "Done"
}
catch [System.Management.Automation.DriveNotFoundException]{
    Write-Host -ForegroundColor DarkYellow "You're WRONG. Here is why:"
    Write-Host -ForegroundColor Yellow $Error[0].Exception.GetType()
    Write-Host -ForegroundColor Magenta $Error[0].Exception
}
catch{
    Write-Host -ForegroundColor DarkYellow "You're WRONG"
    Write-Host -ForegroundColor Yellow "General Exception"
}
finally{
    Write-Host -ForegroundColor Cyan "It's finally over..."
}
        

Try-catch with specific catch block
Next Steps


Last Update:


next webcast button


next tip button



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

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.



    



Monday, June 12, 2017 - 7:40:07 AM - Diana Moldovan Back To Top

Edward, Aaditya - thank you. I'm happy that I could help. This gives me power to write again :)

 

 


Monday, June 05, 2017 - 5:04:18 AM - Aaditya Srivastava Back To Top

Great help with the error action thing. Thanks.


Monday, February 16, 2015 - 12:54:38 PM - Edward Pochinski Back To Top

Great article !!!


Learn more about SQL Server tools