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 (4)

  1. Papy Normand says:

    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 )

  2. Andrew Buschom says:

    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.

  3. Kent Aleksandov says:

    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 someone

  4. subbu - says:

    the returned value is 0 but still job get failed .