The danger of scripting an Agent job

A fast way to create a duplicate of an SQL Server Agent job is to script it, then modify and run the script. But there’s a pitfall.

Somebody clever once asked me “what’s the most recent thing you learned the hard way?” It’s a really great question, because it gives you an idea of when and how accidents happen. Is there a pattern to your work methods that repeatedly cause the problems you run into? Do you have a mental workflow to log and prevent stuff like that from happening again?

Myself, I’d answer this question with “I blog about it”. Writing a short recap may just be what it takes for me to remember something. That, and a bit of shame, of course. So, here goes today’s lesson learned:

Scripting an SQL Server Agent job

If you have a job in SQL Server Agent, you can right-click it in Management Studio and choose “Script job as” > “CREATE to” > “New query editor window…” and you’ll be rewarded with a script similar to this:

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 2014-01-21 15:15:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'A job', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'myLogin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Hello world]    Script Date: 2014-01-21 15:15:49 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Hello world', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SELECT ''Hello world''', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every hour', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=8, 
        @freq_subday_interval=1, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20140121, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959, 
        @schedule_uid=N'8e1a7cc6-c42f-4134-aa5d-35bb179c8442'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Now, to create a duplicate of this job, all you have to do is change the name of the job in the script, and then execute the script.

Ok?

Did you notice this line in the script?

@schedule_uid=N'8e1a7cc6-c42f-4134-aa5d-35bb179c8442'

The job you scriptet has a schedule attached to it. When you script the job and recreate it as a new job, the schedule is automatically inherited, because schedules are separate from jobs. So, if you change the schedule of one of the two jobs, it’ll automatically also update the schedule of the other job.

And I honestly haven’t seen a lot of jobs that can be run concurrently.

Enough said.