Try/Catch and -ErrorAction Stop not functioning as expected

This topic contains 6 replies, has 4 voices, and was last updated by  iain Barnetson 8 months ago.

  • Author
  • #80284

    iain Barnetson

    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.

    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)"
  • #80315


    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?

    • #80360

      iain Barnetson

      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.

  • #80338

    Sam Boutros

    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..)

    • #80362

      iain Barnetson

      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.

  • #80342


    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.

    • #80363

      iain Barnetson

      Point about System.Data.SQLClient.SQLCommand taken and I'm looking at "migrating" on to that.

You must be logged in to reply to this topic.