[Powershell] getting a cmdexec step to fail with useful errors when running a PoSH script

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.