How to fail sql server agent job if powershell script fails

Problem 1

I tried adding a powershell script to a sql server agent job step, but there are some severe limitations where write-host or write-verbose outputs are not allowed instead of ignored, which should make sens. Then the job crashes with the following error:
 A job step received an error at line [xx] in a PowerShell script. The corresponding line is 'write-host "test";  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows.  '.  Process Exit Code -1.  The step failed.

Problem 2

So instead I created a Operating System (CmdExec) step with calling powershell.exe, but it never fails, no matter how bad powershell i write. Just gives false positivies by not detecting step failure. Errors are visibile in the history though.

The following will not fail agent job when powershell script fails

powershell -file "d:\scripts\FailingScript.ps1" -parameter1 value1 -parameter2 value2



Solution

Change the call to -command like below, this will fail correctly if powershell script fails

powershell -command "try { & 'd:\scripts\FailingScript.ps1' -parameter1 value1 -parameter2 value2 } catch { throw $_ }"


Comments

Joel Greijer said…
To communicate verbose messages to Sql Server Agent log, you can change write-verbose and write-host into write-output
08 November, 2017 12:25
Post a Comment

Popular posts from this blog

SSIS: Set parent package variable from child package

SSIS: Set parent package variable from child package For this sample we communicate using bool value named ReturnValue First create a variable in parent package: ReturnValue as Boolean In child package, you do not need to set package configuration for the parent variable, nor should the ReturnValue variable be created. Create script task in child package. Leave the fields ReadOnlyVariables, ReadWriteVariables empty. public void Main() { Variables vars = null ; Dts.VariableDispenser.GetVariables( ref vars); var dummy = false ; if (Dts.VariableDispenser.Contains( "User::ReturnValue" )) { Dts.Events.FireInformation(0, "" , "Setting (parent package variable) User::ReturnValue" , null , 0, ref dummy); Dts.VariableDispenser.LockForWrite( "User::ReturnValue" ); Dts.VariableDispenser.GetVariables( ref vars); vars[ "User::ReturnValue" ].Value = true ; ...
Read more

How to decrypt stored password from SSMS registered servers

Image
Right click your server, choose tasks/export Uncheck "Do not include user names and passwords in the export file" Save the export to a file Open the file in an editor and locate the tag RegisteredServers:ConnectionStringWithEncryptedPassword Copy the contents of password attribute Open powershell and paste the code below, after you amend it with your encrypted string. $base64pass = "PasteEncryptedPasswordHere" [System.Reflection.Assembly]::LoadWithPartialName("System.Security") | Out-Null [System.Text.Encoding]::Unicode.GetString([System.Security.Cryptography.ProtectedData]::Unprotect([System.Convert]::FromBase64String($base64pass), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
Read more

Insert bulk statement does not support recompile (SQL 2017)

This happened to me once, source and target databases was located on the same server running 14.0.1000.169 Microsoft SQL Server Enterprise (64-bit) Consecutive executions never fail. I could not reproduce the error until next day. My workaround has been to simply add "re-try execution" a number of times in SQL Agent job. SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005  Description: "Insert bulk statement does not support recompile.".  End Error
Read more