Powershell & SQL Jobs - Passing errors so job step will fail

Question

Good Day folks,

I hope someone can help me here. I am a complete powershell n00b and I've been tasked with the following.

We have a SQL Server 2019 job which runs several stored procedures and then we have a step which calls a powershell command on a remove server. The command we run currently is:

INVOKE-COMMAND -ComputerName remoteServer -ScriptBlock {D:\EXEs\application.bat 35 0 0 0 [PathDirectory] 0 A}

This batch file is simple. It looks like:

@echo off
set CUR_YYYY=%date:~10,4%
set CUR_MM=%date:~4,2%
set CUR_DD=%date:~7,2%
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)

set CUR_NN=%time:~3,2%
set CUR_SS=%time:~6,2%
set CUR_MS=%time:~9,2%

set SUBFILENAME=%CUR_YYYY%%CUR_MM%%CUR_DD%-%CUR_HH%%CUR_NN%%CUR_SS%

D:\EXEs\InDesignPDFgen3\InDesignPDFgen.exe %1 %2 %3 %4 %5 %6 %7 > D:\EXEs\InDesignPDFgen3\Logs\output-%SUBFILENAME%.log 2> D:\EXEs\InDesignPDFgen3\Logs\error-%SUBFILENAME%.log

All this does is is run the actual application. It also creates 2 text files on every run along with datestamps to the second. One captures the Output when the process runs and the second is an error file. I'm sure this could be done in powershell as well.

This is where we run into issues. The first part of the application pulls data from SQL and then it runs indesign to create a bunch of PDFs. This application will crash at times. Before we had the log files through the batch file in place, we could not tell where the process crashed to pinpoint what happened. But now that we're using the batch file, if it crashes, it logs it via the batch file but our job step in SQL Server passes and allows other things to run with bad data. We need the job to fail if there is a problem but randomly searching does not cover everything we're looking for and I can not seem to get a script to work. I'm sure this all can be done via one powershell script. I'm a SQL developer and to be honest, programming hurts my head. Hoping someone has had a similar experience and can help me here.

Thanks in advance

Answer 1

Powershell will return a zero exit code unless you throw an error. Check $LASTEXITCODE after running the exe and throw if non-zero:

INVOKE-COMMAND -ComputerName remoteServer -ScriptBlock {D:\EXEs\application.bat 35 0 0 0 [PathDirectory] 0 A;if($LASTEXITCODE -ne 0) {throw "Non-zero exit code: $LASTEXITCODE"}}