I found a bug in SSMS that you may run into when modifying multi-step jobs. If you delete the last step of a job, when you click OK, you will get a pop up saying the On Success action of the (new) last step will be changed from “Go to the next step” to “Quit with Success” and asking is this the intended behavior? If you select Yes, the job closes. However, if you open the job again, you’ll see the new last step still has an On Success action of “Go to the next step.” If you run the job, the last step will run, but the job will fail because there is no next step. There is no indication of this in the job history log.
Steps to reproduce:
- Create a new job using SSMS. Add Step 1 to the job consisting of the T-SQL statement “SELECT 1”. Add step 2 to the job also consisting of the T-SQL statement “SELECT 1”. Click OK. You will get a pop up message saying “Note: The On Success action of the last Step will be changed from ‘Goto Next Step’ to ‘Quit with Success’. Is this the intended behavior?” Click Yes.
- Open the job with SSMS again and go to the Steps page. Verify the On Success action of Step 2 has been changed to ‘Quit with Success’.
- Delete Step 2 of the job by highlighting it and clicking the Delete button. Click OK. You will get a pop up message saying “Note: The On Success action of the last Step will be changed from ‘Goto Next Step’ to ‘Quit with Success’. Is this the intended behavior?” Click Yes.
- Open the job with SSMS and go to the Step page. The On Success action of Step 1 is still “Go to the next step.” If you run the job at this point, the job will fail. The job history gives no indication of the reason for the failure and it shows Step 1 completing successfully.
I have reproduced this behavior using SSMS from both SQL 2008 R2 and SQL 2012 and against SQL, 2005, 2008 R2, and 2012 servers.
I’ve submitted a Microsoft Connect item for this. Please vote it up if you experience the same behavior. The Connect item is here. The only other place I’ve found mentioning this behavior is a message board here from back in 2009. The workaround is to manually change the On Success action of the last step.
Shaun,
I did reproduce the issue, but it never reported an error and the Job completed successfully without any issues.
Thanks
Bijoy