February 7, 2024 at 7:32 am
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
February 7, 2024 at 9:16 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 7, 2024 at 9:22 am
Thanks @phil - Looks like I spent time yesterday trying to do the impossible.
Writing to a table it is.
February 7, 2024 at 9:32 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 7, 2024 at 10:28 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply