TSQL - change the execution order of SQL-code in the code

Question

I have several SQL maintenance jobs that have over 50 work steps. Sometimes you need to add a step or two to one of these monstrous SQL jobs. The problem is that I prefer each step in alphabetical order (if possible), but for this I need to press the up arrow countless times and wait for the screen to refresh. I looked at sp_update job procedures and they don't seem to address this issue.

Is there a way this can be done in code?

Answer 1

There is no documented procedure in TSQL or API in SMO to change the order of job steps. If you reorder work steps in SSMS and generate a script action, you will see that it deletes and re-creates all work steps in a new order.

The most obvious solution is to develop your own script or tool to re-order the steps by resetting them and re-creating them, i.e. Re-create SSMS functionality, but optimized for your own needs.

The last resort is to update sysjobsteps.step_id to change the execution order, but changing system tables directly is not recommended.

Answer 2

I found this question trying to reorder the steps without clearing them and re-adding them.

To solve this problem, I have a three-step process: (Assuming that only one step has failed or that all existing steps are in alpha order)

1) If necessary, delete the step you want to reorder. (You should use select for msdb.dbo.sysjobsteps if you need any existing values)

 EXEC msdb.dbo.sp_delete_jobstep @job_id = @YOURJOBID, @step_id = @OUTOFORDERSTEPID 

2) Use the comparison to get the position your new move should be.

 @newStepPosition = SELECT MIN(step_id) FROM msdb.dbo.sysjobsteps WHERE job_id = @YOURJOBID AND step_name > N'YOURNEWSTEPNAME' 

3) Just add a task step by specifying a position. All existing steps will be mixed. for example, an insert of 3 will squeeze from 3 to 4, from 4 to 5, etc.

 EXEC msdb.dbo.sp_add_jobstep @ job_id=@YOURJOBID , @step_name=N'YOURNEWSTEPNAME', @ step_id=@newStepPosition , etc... 

Tested with MS SQL Server 2008 R2. Hope this helps someone else take a look at this.