Throw error without stack trace

• richardmgreen1

  SSChampion

  Points: 10387

  More actions

  February 7, 2024 at 7:32 am

  #4374637

  Hi all

   

  We're using PowerShell V5 on a SQL2022 Enterprise box.

   

  I've got  SQL Agent job step that calls a PS script.

   

  Occasionally, the step fails but doesn't fail the job (it just carries on regardless).

   

  I need this step to fail the job so I've been looking at the PS script and decided to use a THROW command as part of a TRY/CATCH/FINALLY setup.

   

  Unfortunately, the THROW command gives me a lot extraneous info (line/character numbers, script name, etc).

   

  This is an example of the text from the job step (I've highlighted the bits I need to keep):-

  Executed as user: RDGH\BISQL. Environment : Public TenantId : ClientId : **************** Password : ********#####Workspace not found##### At \\RFT-DWPROD\PowerShellScripts\PowerBI\RefreshDataset - RG.ps1:63 char:5 + throw $ErrorMsg + ~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (#####Workspace not found#####:String) [], Runtime Exception + FullyQualifiedErrorId : #####Workspace not found#####. Process Exit Code 1. The step failed.

   

  I've just noticed the bit I've highlighted appears 3 times in the text, I only need one of them.

  Is there any way of throwing an error without all that extra stuff (I've got specific messages to throw to give us something readable)?

   

  TIA

   

  Richard

  • This topic was modified 1 year, 4 months ago by richardmgreen1. Reason: Extra info

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  February 7, 2024 at 9:16 am

  #4374670

  I don't think you can control the format of Agent history messages. But maybe you could write any errors to a table and then query that.

  ---

• richardmgreen1

  SSChampion

  Points: 10387

  More actions

  February 7, 2024 at 9:22 am

  #4374673

  Thanks @phil - Looks like I spent time yesterday trying to do the impossible.

  Writing to a table it is.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  February 7, 2024 at 9:32 am

  #4374693

  richardmgreen1 wrote:

  Looks like I spent time yesterday trying to do the impossible.

  I've been having lots of days like that recently. Then again, I am working with Azure Data Factory.

  ---

• Johan Bijnens

  SSC Guru

  Points: 135510

  More actions

  February 7, 2024 at 10:28 am

  #4374714

  If I'm corrent you can use "Return 1" (or higher) to fail your ps script in sqlagent

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me