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
    };
    continue
  }

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

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.

Allen