How do I show an error in a PowerShell step called from a SQL Server 2008 R2 job?

Question

I look online, but did not find very much. In fact, most of the information was here in stackoverflow - Show a crash for SQL 2005 Job running Powershell script via CMD prompt, IF PS script does not work but what with SQL Server 2005, I hope the situation improved in 2008.

In any case, I want the SQL Server Agent to work FAILURE if the Powershell script fails. In SQL Server, I use the powershell step, only with this:

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

I thought that this would lead to an error in SQL Server, but this does not happen, it shows successful. Do I need to use the cmdexec step and then lay out the shell in powershell to be able to receive errors when they occur?

thanks Sylvia

Answer 1

I am adding a line to Write-Error with setting ErrorAction to stop if I want the Powershell job step to return an error. This is only one line.

 Write-Error "Job Failure" -EA Stop 

Answer 2

Citation Ed Wilson 10th Review "10. Resolving Windows Powershell Errors in SQL Server Agent Jobs" from " 10 Tips for a SQL Server PowerShell Script :

10. Windows Powershell error handling in SQL Server Agent

By default, the ErrorActionPreference parameter is set to Continue, and this has consequences for how errors go to the SQL Server job server. If you run the Windows PowerShell command as a SQL Server agent job and there are no syntax errors yet, the command throws an error (for example, trying to get information about the operating system from an unavailable server). The SQL Server Agent job will report success. If you want the error condition to terminate the SQL Server Agent job or cause an error, you will need to add some error handling. You can configure the SQL Server Agent job during Windows PowerShell as follows:

 get-wmiobject Win32_OperatingSystem -ComputerName 'nothere' 

The task will succeed, but if you run it directly in Windows PowerShell, you will 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 create Windows PowerShell error bubbles for the SQL Server Agent, you must do one of the following:

but. Set $ ​​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 run SQL Server Agent

Say you have a collection of computers and you want to continue the error, but you also want to fail. In this case, you can use 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 3

I also had success in completing the SQL job step using the following code:

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

This was used to check for files in the directory ($ files), and then to eliminate the error. It will return a custom THROW and return a PowerShell exit code (via% errorlevel%), forcing your job to return the error to the SQL agent session that is running the job and completing the job at this point.

Answer 4

If you use the “CmdExec” step instead of the “PowerShell” step (which you should do due to reasons of PowerShell versions ), the following syntax worked for me:

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

(from Joel Greyer ).