THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Handling Errors in PowerShell

Scripting tasks in PowerShell is a fairly easy way to automate tasks, but you have to know when you get errors and what the real error is. There have been quite a number of blog posts on how to handle errors in PowerShell, like this one by Joel 'Jaykul' Bennett. What many of them don't do is deal with the "nested" errors that .NET throws for SQL errors.

For example, the Database object in SMO provides the CheckTables() method, which runs the DBCC CHECKDB command. The problem is that if an error occurs PowerShell just returns a message like

Exception calling "CheckTables with "1" argument(s): "Check tables failed for Database 'AdventureWorks'."

The important thing to remember is that the Error object captured by the Trap has a property called InnerException, and as long as this has a value, there's more to be found. By iterating through the inner exceptions we can find the real message we need. Here's an example:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

Trap {
$err = $_.Exception
while ( $err.InnerException )
$err = $err.InnerException
write-output $err.Message

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'SQLTBWS\INST01'
$db = $s.Databases['AdventureWorks']

This script runs DBCC CHECKDB(N'AdventureWorks', REPAIR_FAST), as you can see by watching it using Profiler. The error gets thrown if you don't have AdventureWorks in Single User Mode. The console output of this script then becomes

An exception occurred while executing a Transact-SQL statement or batch.
Repair statement not processed. Database needs to be in single user mode.

That's a whole lot more useful than the first message we received.


Published Monday, June 8, 2009 9:41 AM by AllenMWhite
Filed under: ,



Chad Miller said:

Thanks for posting this great tip! SMO error handling finally makes sense to me now.

June 22, 2009 10:27 PM

Michelle Ufford said:

Allen, you are awesome. Thank you for this post, you have saved me a lot of error-handling headaches. :)

March 27, 2013 2:56 PM

happy said:

thank you

July 11, 2016 11:05 AM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement