THE SQL Server Blog Spot on the Web

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

Allen White

SQL Agent Error Handling with PowerShell

When you run a PowerShell script in a SQL Server Agent task (or JobStep, as the SMO object is called) I've figured out that when the step fails, Agent records the PowerShell error (as in what line in the script failed), but I've been sending my detailed error messages to the console via Write-Output. This caused a lot of frustration in trying to figure out what the problem really was.

Because I like to have a standard way of building scripts (call me lazy, my wife certainly does) I didn't want to figure out where on the filesystem I could put a home-grown log file. However, every Windows system has an Application Event Log that we can use! Also, because PowerShell sits on top of .NET, we can easily take advantage of the tools available in the framework.

Now, when the errors occur, I've instantiated a Server object like this (where $inst contains the instance name):

# Connect to the specified instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst

And I've already documented looping through the error messages here. So, now it's just a matter of adjusting the code to use the WriteEntry method of the Diagnostics.EventLog object to write to event log, like this.

# Handle any errors that occur
Trap {
  # Handle the error
  $err = $_.Exception
  $errmsg = $err.Message
  while( $err.InnerException ) {
  	$err = $err.InnerException
  	$errmsg = $errmsg + "|" + $err.Message
  [Diagnostics.EventLog]::WriteEntry($s.Name ,"Job Error: $errmsg","Error")
  # End the script.

Once this is done, the message is written to the Application log. It'll have a strange message like this at the start:

The description for Event ID ( 0 ) in Source ( Application ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event:

This is because your application isn't registered with Windows, which is ok. After that block you'll see your actual error message. For example:

Check tables failed for Database 'TicketsalesDB'. |An exception occurred while executing a Transact-SQL statement or batch.|Object ID 354100302, index ID 0, partition ID 72057594039107584, alloc unit ID 72057594043367424 (type In-row data): Page (1:65) could not be processed. See other errors for details. Table error: Object ID 354100302, index ID 0, partition ID 72057594039107584, alloc unit ID 72057594043367424 (type In-row data), page (1:65). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4. CHECKDB found 0 allocation errors and 2 consistency errors in table 'CorruptTable' (object ID 354100302). CHECKDB found 0 allocation errors and 2 consistency errors in database 'TicketsalesDB'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TicketsalesDB)..

This was from a CheckDB failure (which I expected because I use a corrupt database to test Database Mirroring).

Now you can use this method to get the details of errors that occur from within PowerShell scripts in your Agent Jobs.


Published Monday, November 15, 2010 2:15 PM by AllenMWhite
Filed under: ,



Chad Miller said:

You can also use a try/catch block. Here's a snipset of code from SQLPSX:

 try { <do somthing> }

       catch {

               $ex = $_.Exception

               $message = $ex.message

               $ex = $ex.InnerException

               while ($ex.InnerException)


                   $message += "`n$ex.InnerException.message"

                   $ex = $ex.InnerException


               Write-Error $message


November 15, 2010 2:04 PM

AllenMWhite said:

Thanks, Chad, but for now I'm writing my scripts using PS1.0 methods for sites that haven't installed 2.0.  I looked at Write-Error and found it depended on 2.0, so shelved it until after Denali is released.

November 15, 2010 2:13 PM

Nick Weber said:

Thanks Allen for posting this update to your TRAP. Since I'm running 2.0 I might start playing more with Chad example.


November 19, 2010 12:38 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