TL;DR: put
$ErrorActionPreference="Stop" at the top of your script
I feel like a dunce.
I couldn't get a Powershell script to fail, when it had errors, when invoked as a "Operating system (CmdExec)" step in SQL Server Agent. The first script I wound up rewriting as a PsExec (aka SQL Powershell Provider) script, but this second one I already had working in the ISE.
#save this one line as c:\blah.ps1
get-content c:\narf\poit.txt
And a job step as "Operating System (CmdExec)" (not scripting it out since it's 50 unnecessary lines):
powershell "c:\blah.ps1"
I know the file is bad - in fact, the directory doesn't even exist!
Hence, it crashes in the ISE:
get-content : Cannot find path 'C:\narf\poit.txt' because it does not exist.
At \c:\temp\blah.ps1:4 char:1
+ get-content c:\narf\poit.txt
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (C:\narf\poit.txt:String) [Get-Content], ItemNotFoundException
+ FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand
However, when run via SQL Agent, it succeeds. GAH!
I tried 50 different variations; modifying the script, various TRY..CATCH blocks found on the internet. Nothing. Every single one of them succeeded.
Then I remembered that by default, even though it had an error, by default errors always continue. ($ErrorActionPreference="Continue". So I added this line at the top:
#save these TWO lines as c:\blah.ps1
$ErrorActionPreference = "Stop"
get-content c:\narf\poit.txt
Lo and behold, now it fails. DOH!
So, since we're here, let's try using some of the blocks I found on the internet and see what they return.
- https://social.technet.microsoft.com/Forums/windowsserver/en-US/066eab18-1105-4462-ae70-8efeb8510ccc/sql-job-not-failing-when-powershell-script-runs-and-fails?forum=winserverpowershell
- http://stackoverflow.com/questions/9111752/how-to-show-error-in-powershell-step-called-from-sql-server-2008-r2-job
- http://www.sqlservercentral.com/Forums/Topic1662248-1351-1.aspx
- Just running the code, nothing fancy:
Executed as user: thebakingdba. Get-Content : Cannot find path 'C:\narf\poit.txt' because it does not exist. At c:\this_will_fail.ps1:4 char:12 + get-content <<<< c:\narf\poit.txt + CategoryInfo : ObjectNotFound: (C:\narf\poit.txt:String) [Get-C ontent], ItemNotFoundException + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetCo ntentCommand. Process Exit Code 1. The step failed.
- if I use a TRY..CATCH block, slightly different:
try
{
get-content c:\narf\poit.txt
} catch {
write-error $_
[System.Environment]::Exit(1)
}
Message:
Executed as user: thebakingdba. c:\this_will_fail.ps1 : Ca nnot find path 'C:\narf\poit.txt' because it does not exist. At line:1 char:75 + c:\this_will_fail.ps1 << << + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorExcep tion + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorExceptio n,this_will_fail.ps1. Process Exit Code 1. The step failed.
- Thie results of this TRY..CATCH block is identical to running it without the TRY..CATCH:
} catch {
$output = $_.ErrorDetails
throw $output
}
- This fourth example is where it gets interesting, but for my needs not terribly useful. More info, but it's more "internals".
} catch {
$ex = $_.Exception
throw "$ex.Message"
}
Executed as user: thebakingdba. System.Management.Automation.ItemNotFoundException: Cannot find path 'C:\narf\p oit.txt' because it does not exist. at System.Management.Automation.LocationGlobber.ExpandMshGlobPath(String pat h, Boolean allowNonexistingPaths, PSDriveInfo drive, ContainerCmdletProvider pr ovider, CmdletProviderContext context) at System.Management.Automation.LocationGlobber.ResolveDriveQualifiedPath(St ring path, CmdletProviderContext context, Boolean allowNonexistingPaths, Cmdlet Provider& providerInstance) at System.Management.Automation.LocationGlobber.GetGlobbedMonadPathsFromMona dPath(String path, Boolean allowNonexistingPaths, CmdletProviderContext context , CmdletProvider& providerInstance) at System.Management.Automation.PathIntrinsics.GetResolvedPSPathFromPSPath(S tring path, CmdletProviderContext context) at Microsoft.PowerShell.Commands.ContentCommandBase.ResolvePaths(String[] pa thsToResolve, Boolean allowNonexistingPaths, Boolean allowEmptyResult, CmdletPr oviderContext currentCommandContext).Message At c:\this_will_fail.ps1:1 8 char:6 + throw <<<< "$ex.Message" + CategoryInfo : OperationStopped: (System.Manageme...ontext).Mes sage:String) [], RuntimeException + FullyQualifiedErrorId : System.Management.Automation.ItemNotFoundExcepti on: Cannot find path 'C:\narf\poit.txt' because it does not exist. at System.Management.Automation.LocationGlobber.ExpandMshGlobPath(Strin g path, Boolean allowNonexistingPaths, PSDriveInfo drive, ContainerCmdletP rovider provider, CmdletProviderContext context) at System.Management.Automation.LocationGlobber.ResolveDriveQualifiedPa th(String path, CmdletProviderContext context, Boolean allowNonexistingPat hs, CmdletProvider& providerInstance) at System.Management.Automation.LocationGlobber.GetGlobbedMonadPathsFro mMonadPath(String path, Boolean allowNonexistingPaths, CmdletProviderConte xt context, CmdletProvider& providerInstance) at System.Management.Automation.PathIntrinsics.GetResolvedPSPathFromPSP ath(String path, CmdletProviderContext context) at Microsoft.PowerShell.Commands.ContentCommandBase.ResolvePaths(String [] pathsToResolve, Boolean allowNonexistingPaths, Boolean allowEmptyResult , CmdletProviderContext currentCommandContext).Message. Process Exit Code 1. The step failed.
- Another variation, this one just throwing an error, and trying to throw the error level: (It doesn't give the level)
} catch {
THROW 'Throw Error: no file!'; %errorlevel% #the quotes don't seem to matter; w/ and w/o both wrong
}
Executed as user: thebakingdba. Throw Error: no file! At c:\this_will_fail.ps1:2 3 char:6 + THROW <<<< 'Throw Error: no file!'; %errorlevel% + CategoryInfo : OperationStopped: (Throw Error: no file!:String) [], RuntimeException + FullyQualifiedErrorId : Throw Error: no file!. Process Exit Code 1. The step failed.
- One that a coworker developed that I hadn't known of:
}catch{
throw $_.Exception.Message
exit 1
Break
}
Executed as user: thebakingdba. Cannot find path 'C:\narf\poit.txt' because it does not exist. At c:\this_will_fail.ps1:2 6 char:7 + throw <<<< $_.Exception.Message + CategoryInfo : OperationStopped: (Cannot find pat...does not ex ist.:String) [], RuntimeException + FullyQualifiedErrorId : Cannot find path 'C:\narf\poit.txt' because it d oes not exist. Process Exit Code 1. The step failed.
