Try/Catch and -ErrorAction Stop not functioning as expected

PowerShell Help
1

I’ve a PS script that runs a tsql script, passing it parameter values, encapsulated in Try/Catch with " -ErrorAction STOP". When I run this manually, it works as expected and the steps in the catch are processed as expected. However, when I run this as a scheduled task, with an AD account that has necessary privileges, it fails but the catch is not processed, nothing happens.
Why?

try {
# Excute the command
Invoke-SqlCmd -Variable $parameters -InputFile $SSN_Change -ServerInstance $Srv –Database $db -querytimeout ([int]::MaxValue) -ErrorAction Stop
} catch {
Logwrite  "Updating SSN $OldSSN to $NewSSN in $db on $Srv failed`r`nError:$($_.Exception.Message) - Line Number: $($_.InvocationInfo.ScriptLineNumber)"
Add-MantisNote -ticket $ticket -id $id -Note "Updating SSN $OldSSN to $NewSSN in $db on $Srv failed`r`nError:$($_.Exception.Message) - Line Number: $($_.InvocationInfo.ScriptLineNumber)"
Continue 
}
2

Stab-in-the-dark theory: The “Logwrite” function throws an exception, aborting the Catch block.

Are you running the manual test as the same user that runs the scheduled task?

3

where is the ‘logwrite’ function? Is it in a module you have on the same machine running the task? Is that module under the general $env:CommonProgramFiles folder where the scheduled task can reach it? Does this modules have dependencies/#Require statements that the scheduled task can get?

I would put a simple

'We''re at the catch block' | out-file c:\somepath\test.txt

before the ‘logwrite’ line just to make sure that the scheduled task is getting to this point (if the test.txt file exists…)

4

Is the scheduled task actually showing a failure? I would also turn on transcription so you can see what’s going when you run the code as a scheduled task. Also, the Invoke-sqlcmd has terrible error handling, (bugs). I suggest using .NET System.Data.SQLClient.SQLCommand. There is more code involved for the connection but you get more reliable error handling.

5

The Catch is not being fired, I don’t think, doesnt seem it’s getting there.
Admittedly I’ve not run it as same user, as the service account cannot logon locally, but it & I have same rights on the systems in question. And the the module in which this is part of a Function in, has exactly the same Try…Catch, and Logwrite, etc in over 40 Functions and all work as expected under the service account.

6

All the PS code is in same Module, a heap of Functions in it, located in standard place: C:\Windows\System32\WindowsPowerShell\v1.0\Modules
Been using them all for nearly two years and no problems untill now. In fact the Function this step is part of I’ve been happily running for months. Just got this issue 2 days ago and a bit baffled why, when there’s been issues before the Try…Catch functioned as expected.
As yet I’ve not logged on using the service account and run it manually under those creds, as logon locally is denied, have to raise ticket to get that allowed. A runas worked fine and ran successfully.
I’ve not been able to recreate the problem but don’t understand why the Try…Catch and -ErrorAction Stop didn’t function as expected.

7

Point about System.Data.SQLClient.SQLCommand taken and I’m looking at “migrating” on to that.