Can I make the PowerShell $error object behave consistently in both script and interactive mode?
===

I'm writing a PowerShell script that uses `$error` to detect and respond to errors.  My problem is that I get different behavior for the `$error` object depending on how I run the script.  If I run it interactively (specifically, from PowerShell ISE), then errors get added to the collection, but if I run the same script from the command line, the same errors occur, but are not added to the collection.

Here's the script (pared down to illustrate the issue):

    # export_exception_test.ps1
    
    # show me how many errors before we start
    "Count = " + $error.Count
    
    try {
        # treat non-terminating errors as terminating errors.
        $ErrorActionPreference = "Stop"
        # echo the setting to output
        $ErrorActionPreference
        
        # use sqlcmd to save the output of stored procedures to text files.
        # The first and third call will fail because the output folder does not exist.
        # The second call will succeed.
        'first call to sqlcmd'
        sqlcmd -S myserver\myinstance -E -s `"`t`" -Q "EXEC mydatabase.dbo.FI_Codes" -b -o "B:\Exports_new\FI_Codes.txt"
        'second call to sqlcmd'
        sqlcmd -S myserver\myinstance -E -s `"`t`" -Q "EXEC mydatabase.dbo.FI_Codes" -b -o "B:\Exports_newt\FI_Codes.txt"
        'third call to sqlcmd'
        sqlcmd -S myserver\myinstance -E -s `"`t`" -Q "EXEC mydatabase.dbo.FI_Codes" -b -o "B:\Exports_new\FI_Codes.txt"
        # and a whole bunch more of these...
    
        # The error count should be two more than when we started.
        "Count = " + $error.Count
        # And this should be the most recent error message
        "Message = " + $error[0].Message
    
    }
    catch [Exception]
    {
        'exception was caught!!!'
        "Count in catch clause = " + $error.Count
        $_.Exception.Message
        # the ultimate goal is to return a non-successful return code when the exports fail
        exit 1
    }
    finally {
        # set this back to what it was
        $ErrorActionPreference = "Continue"
    
        # primitive trace output
        'finally.'
    }

When I run this from PowerShell ISE, it behaves as expected.  SQLCMD raises a non-terminating error, which is caught and handled.  Here's the output:


    PS U:\> C:\Users\etmatt\Documents\PowerShellScripts\export_exception_test.ps1
    Count = 0
    Stop
    first call to sqlcmd
    exception was caught!!!
    Count in catch clause = 1
    Sqlcmd: Error: Error occurred while opening or operating on file B:\Exports_new\FI_Codes.txt (Reason: The system cannot find the path specified).
    finally.

But when I run it from the command line, as I would if I were to set up a scheduled task, nothing gets added to `$error`, and no exceptions are raised.  Here's the output:

    C:\Users\etmatt>powershell.exe -file "C:\Users\etmatt\Documents\PowerShellScripts\export_exception_test.ps1"
    Count = 0
    Stop
    first call to sqlcmd
    Sqlcmd: Error: Error occurred while opening or operating on file B:\Exports_new\FI_Codes.txt (Reason: The system cannot find the path specified).
    second call to sqlcmd
    third call to sqlcmd
    Sqlcmd: Error: Error occurred while opening or operating on file B:\Exports_new\FI_Codes.txt (Reason: The system cannot find the path specified).
    Count = 0
    Message =
    finally.
    
    C:\Users\etmatt>

I also get the same results if I run the script file from the powershell command line rather than cmd.exe (which makes sense).  For example:

    PS C:\> ."C:\Users\etmatt\Documents\PowerShellScripts\export_exception_test.ps1"

I suspect this has something to do with the execution context or maybe something about parser modes that I'm still not quite grokking, or maybe even my profile (although so far I've been running everything from the same PC under the same account).  I've seen a lot of examples online that use this basic approach of `try/catch` with `$ErrorActionPreference = "Stop"`, so it seems like I should be able to make this work.

So my question is essentially: can I make this script work like I think it should?  And if not, what am I misunderstanding? How do I catch these sorts of errors? If it helps, I don't need super detailed exception handling for this one, I just need to know when something goes wrong so that my task monitor can alert me.  A non-zero return code from powershell.exe would be sufficient.

I'm looking into the use `$?`, and `$LASTEXITCODE`, but I'm betting `$?` will have the same issue as `$error`, and both only apply to the last statement executed, which is less than ideal since my real script is quite a bit longer than this example.


