Job steps not in order. Why?
-
Hello,
maybe anybody had something similar or have any ideas.
I ran ActiveX script to script out all Jobs from PROD server. Job ran fine, I got my script.
I copied the same Job to different (TEST)server and ran it there. I got output with all Jobs, but steps were mixed up! Something like this :
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID , @step_id = 4, @cmdexec_success_code = 0, @on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 4, @on_fail_step_id = 13, @retry_attempts = 0, @retry_interval = 1, @os_run_priority = 0, @flags = 2, @step_name = N'BackupDB', if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID , @step_id = 3, @cmdexec_success_code = 0, @on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 4, @on_fail_step_id = 13, @retry_attempts = 0, @retry_interval = 1, @os_run_priority = 0, @flags = 2, @step_name = N'DBS_KILLUSERS', @subsystem = N'TSQL', @command = N'exec master..DBS_KILLUSERS if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID , @step_id = 5, @cmdexec_success_code = 0, @on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 4, @on_fail_step_id = 13, @retry_attempts = 0, @retry_interval = 1, @os_run_priority = 0, @flags = 2, @step_name = N'DBS_killUsers 2', @subsystem = N'TSQL', @command = N'exec master..DBS_KILLUSERS if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID , @step_id = 7, @cmdexec_success_code = 0, @on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 4, @on_fail_step_id = 13, @retry_attempts = 0, @retry_interval = 1, @os_run_priority = 0, @flags = 2, @step_name = N'DropUsers', @subsystem = N'TSQL', @command = ............
I tried couple times - the same mix again.
I told someone from my team to do the same. That person copied the same job from PROD to this server, ran it and output was fine. When I ran the same job after output was OK again and I am not able to reproduce that crazy error again. Everything works now and no problem, but I would like to know WHY that sh.t happened???!!!
Thanks!
-
Were the steps mixed up on the server when you opened the job or just in the script when output?
-
Only in the output script. In the Jobs - all steps are in the order. Like I said, later we were able to get successfull script.
I am just wondering what it was...Maybe just "Monday" ?
-
OH! I FOUND SOMETHING!!!
from Internet :
I have come across what seems to be a bug in SQL 2K SP3.
When I have a job with multiple job steps and I right-click to choose which step I want to start with, the job steps are in no particular order.
I am also using an ActiveX script that uses SQLDMO to script out the jobs to a text file. When the job is scripted, it puts the job steps in what seems like alphabetical order by step name and not in step number order. If you try to run this script to create the job, it will fail because it requires the job steps to be in step number order.
I have talked to others that have used this script prior to SP3 so it seems to only have occurred in this Service Pack.
I just wanted to get this information out there so it can be addressed and corrected in either a patch or at least the next SP.
Reply
This is indeed a known bug for SQL2000. Its because sp_help_jobstep doesn't
contain an order by when returning results IIRC. Generally you get the right
result but without the order by its not guaranteed
SP3 - BAD DOG!
-
I just applied service pack 4 and when I right click on a job and select start job, the dialog box comes up in step name order, not step id?
Anybody know why?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply