Here’s a simple example of how you can set up a SQL agent job step to pass or fail based on a batch file’s execution results.
The Batch File
Let’s set up a sample batch file, save it in “c:\test\test_fail_batch_.bat”, and add the following code to it:
move "c:\test\test.txt" "c:\test\test2.txt" rem 0 is usually an indicator of success for a process rem checking "if errorlevel 1" will return true if errorlevel is >= 1 if errorlevel 1 EXIT 1 EXIT 0
First, a simple move operation is performed on a file. The move operation could be interchanged with any other executable that you will be running. We know that the move command returns a code of 0 if it succeeds or a positive integer if it fails.
After the move command, we add a little control logic to make the batch file return a positive error code 1 if the move failed or a 0 error code if the move succeeded. You could of course return a positive error code and exit on the move failure and continue to do other batch operations on success instead if you have more operations that you will be performing in the batch file.
I’ll note here that it’s a good idea to test the return code output of the executable you are running to make sure it returns what you expect. It’s unusual, but I have run into a couple of apps in the past where 0 was not returned for success. How you want to handle certain non-success messages may also change per executable. For example, at one time I was using the command line version of the open source archiving utility 7-zip to handle the extraction of incoming zip files. At the time, 7-zip was able to extract the archives just fine, but the way the zip files were compressed before I received them resulted in the application issuing a warning message. The warning message was perfectly valid, but because of the warning the app was returning 1 instead of 0, which is the return code 7-zip uses to denote that a warning had been issued. Knowing why the app was returning a 1 and accepting that in this particular case it should still indicate success of the operation, I could then change my logic to look for a 2 or above to decide if a real error I wanted to catch had occurred.
The SQL Agent Job
OK, moving on, let’s create a sample SQL agent job called “test batch file fail”. Add a single step of type “Operating System (CmdExec)”. For the step’s command add the path to your batch file, “c:\test\test_fail_batch_.bat”. And for the Process exit code of a successful command option, make sure it has 0. As noted in the batch file comments, 0 is usually the error level returned by a process when the process ran successfully, and error levels of an integer greater than 1 usually indicate some type of failure.
Now try running your test job. When you run the job and the file “c:\test\test.txt” that the batch file is trying to move doesn’t exist, the move command will return with a value greater than 0 indicating an error condition. The batch script will then see that the errorlevel of the move command was greater than 0, so it will exit the batch job delivering 1 as the batch’s return code. The SQL agent job step will see that 1 is different than the 0 you set as its process exit code of a successful command option, so the step and consequently the job will fail.
Try running the job again, but make sure you’ve created a dummy “c:\test\test.txt” file first. Now, with a file available to move, the move command in the batch file will succeed, the batch job will exit delivering a 0 as it’s return code, and therefore the SQL agent job will succeed.
There you have it, a simple walk-through of how to set up SQL agent job steps to pass or fail based on a batch file’s execution results. Feel free to share if you have any other cool SQL agent tricks or methods related to bat files.