How to show error in PowerShell step called from SQL Server 2008 R2 job?

Question

I've been looking around online, but haven't found very much. As a matter of fact, the most info has been here on stackoverflow - Show failure for SQL 2005 Job that executes Powershell script through CMD prompt, IF PS script fails but that's with SQL Server 2005, I'm hoping things have improved in 2008.

Anyway, I want to be able to have the SQL Server Agent job FAIL if the the Powershell script fails. In SQL Server I'm using a powershell step, with just this:

write-output "this is a test error"
exit -1

I thought this would cause the SQL Server job to error, but it's not happening, it shows successful. Is it necessary to use a cmdexec step, and then shell out to powershell, in order to be able to get errors when they occur?

thanks, Sylvia

Answer 1

Citing Ed Wilson's 10th tip "10. Handle Windows Powershell errors in SQL Server Agent jobs" from "10 Tips for the SQL Server PowerShell Scripter":

10. Handle Windows Powershell errors in SQL Server Agent jobs

By default the ErrorActionPreference is set to Continue, and this has implications on how errors bubble up to the SQL Server Job Server. If you run a Windows PowerShell command as a SQL Server Agent job and there are no syntax errors yet, the command produces an error (for example, attempting to get operating system information from an unavailable server). The SQL Server Agent job will report success. If you want an error condition to halt execution of a SQL Server Agent job or to produce an error, you'll need to add some error handling. You can set up a SQL Server Agent job with Windows PowerShell Job Step as follows:

get-wmiobject Win32_OperatingSystem -ComputerName 'nothere'

The job will run successfully, but if you run it directly in Windows PowerShell, you'll see:

get-wmiobject Win32_OperatingSystem -ComputerName 'nothere'
get-wmiobject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

At line:1 char:1

  + get-wmiobject Win32_OperatingSystem -ComputerName 'nothere'

To bubble up Windows PowerShell errors to SQL Server Agent, you'll need to do one of the following:

A. Set your $ErrorActionPreference = "Stop"

  $erroractionpreference = "Stop"
  get-wmiobject Win32_OperatingSystem -ComputerName 'nothere'

B. Set ErrorAction at the cmdlet-level (more granular)

   get-wmiobject Win32_OperatingSystem -ComputerName 'nothere'  -ErrorAction 'Stop'

C. Use Try/Catch with ErrorActionPreference or ErrorAction

try 
{  
  get-wmiobject Win32_OperatingSystem -ComputerName 'nothere' -ErrorAction 'Stop'  

}  
catch 
{   
  throw "Something went wrong"  
  #or rethrow error  
  #throw $_  
  #or throw an error no message  
  #throw  
}  

D. Continue, and fail the SQL Server Agent job

Let's say you have a collection of computers and you want to continue on the error, but you also want to fail the job. In this case you can make use of the ErrorVariable:

  #Note the -ErrorVariable parameter takes a variable name without the $ prefix.
  get-wmiobject Win32_OperatingSystem -ComputerName 'localhost','nothere','Win7boot' -ErrorVariable myError

  if ($myError)
  { throw ("$myError") }

Answer 2

I add a line to Write-Error with ErrorAction set to stop if I want a Powershell job step to return an error. It's just the one line.

Write-Error "Job Failure" -EA Stop

Answer 3

If you are using a "CmdExec" step instead of a "PowerShell" step (which you should, due to PowerShell versioning reasons), the following syntax has worked for me:

powershell.exe -command "try { & 'D:\BadScript.ps1'} catch { throw $_ }"

(from Joel Greijer).

Answer 4

I've also had success in terminating a SQL job step by using the following code:

    if (!$files) 
    {THROW 'Throw Error: the backup directory was empty!'; "%errorlevel%"} 

This was used to check if there were files in a directory ($files), and then throw an error if empty. It will return the custom THROW and return the PowerShell exit code (via %errorlevel%), causing your job to return an error to the SQL Agent session running the job and ending the job at that step.