Thursday 21 November 2013

Executing powershell script in a SQL Agent job - Host errors

Recently I created a SQL Agent job that’s runs a PowerShell script, I tested the PowerShell script in my PowerShell editor and the script was running fine, but when I ran the same script in SQL agent job it failed with the following error

The corresponding line is '$space.ForegroundColor = $host.ui.rawui.ForegroundColor'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception setting "ForegroundColor": "Cannot convert null to type "System.ConsoleColor" due to invalid enumeration values. Specify one of the following enumeration values and try again. The possible enumeration values are "Black, DarkBlue, DarkGreen, DarkCyan, DarkRed, DarkMagenta, DarkYellow, Gray, DarkGray, Blue, Green, Cyan, Red, Magenta, Yellow, White"."

This error happens when you have a cls or clear-host command on your script as PowerShell run under SQL Agent job does not like any of the host commands. Once I cleared I received another error which was related to Host commands,

 A job step received an error at line 107 in a PowerShell script. The corresponding line is ' if($sleepcount -ne 2){write-host "Waiting for 10 Seconds...........";Start-Sleep 10}'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot invoke this function because the current host does not implement it.  '.  Process Exit Code -1.  The step failed.

Similar to our first error this was due to write-host commands in my script I had to comment all my write-host commands in my script to run the job successfully.

No comments:

Post a Comment