SQL Server Agent job returns a failure status after success execution
I have seen this question in forums repeatedly. The job is set to copy some files over the network, the files get copied but the job status shows a failure.
The reason of the failure status is that SQL Server Agent uses the exit code returned by the program to determine if the job step executed successfully and the default value is 0 (zero) for success. You can see this default value (and change it) by opening the job Properties from SSMS and selecting Edit in the corresponding job step. The job type should be "Operating System (CmdExec)" and right before the Command window, you should see it: Process exit code of a successful command: 0
If the program you are using returns anything other than 0 on success, you should change that value to reflect correctly what the status of the job step is after execution.
When I've seen related questions in the forums, in every case the user was using Robocopy to copy the files. Now, Robocopy returns 0 only when no files were copied. If files were copied, the return value is 1.
Comments
Anonymous
October 11, 2012
Thanks for the explanation about the little problem caused by Robocopy ( i ignored it ) Thanks also for the screenshot which makes easier the understanding of your post. A tiny reproach : at least one link towards the concerned threads would be useful I hope to see in the future more posts like this one : a real pleasure ( and it is not a joke )Anonymous
January 16, 2013
What if there are two values that could mean success? To me, zero and one both mean that the step was successful but agent will only accept a single value for success.Anonymous
October 03, 2013
As a solution for this problem you could create a Batch file and return whatever codes you need. I know it's an old question, but I stumbled upon it having the same issue, and then came up with this solution so hope it helps someoneAnonymous
March 09, 2015
the returned value is 0 but still job get failed .