1

I have a SQL Server Agent Job which is designed to reconfigure itself in response to conditions that are in place when the job starts.

So, in essence, the first step updates the second step. That happens correctly, but it looks like the SQL Server Agent process is unaware the second step has been updated, and runs the old, cached copy of the step-2 command. I've tried adding sp_update_job to the first step in an attempt to get the Agent process to update its cache, but not joy.

Here's an mcve for your view pleasure:

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
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'Test self-updating 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'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step 1 - update step 2', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @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'USE [msdb];
GO
DECLARE @job_name sysname = N''Test self-updating job'';

DECLARE @command nvarchar(max);
IF EXISTS
(
    SELECT 1
    FROM [msdb].[dbo].[sysjobsteps] [sjs]
        INNER JOIN [msdb].[dbo].[sysjobs] [sj] ON [sjs].[job_id] = [sj].[job_id]
    WHERE [sj].[name] = N''Test self-updating job''
        AND [sjs].[step_id] = 2
        AND [sjs].[command] = N''PRINT N''''A'''';''
)
BEGIN
    SET @command = N''PRINT N''''B'''';''; 
END
ELSE
BEGIN
    SET @command = N''PRINT N''''A'''';''; 
END;
EXEC [msdb].[dbo].[sp_update_jobstep] @job_name = @job_name, @step_id = 2, @command = @command;
EXEC [msdb].[dbo].[sp_update_job] @job_name = @job_name, @automatic_post = 1;
', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step 2 - do the work', 
        @step_id=2, 
        @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'PRINT N''A'';', 
        @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_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:
GO

The test job starts out with the 2nd step alternating between printing A or B each time it runs. i.e. the first time it runs, it should print B, then the next time it runs it should print A, then the next time it runs, it should print B and so on.

Running the job, and checking the job history shows the output is A, however inspecting the command for the 2nd step shows it being PRINT N'B';. So, the step command is being updated, but the Agent is actually running the old pre-updated step code.

Is there a way to get SQL Server Agent to run the updated code in step 2 instead of the old out-of-date code?

In the real job, the 2nd step is cmdExec command that runs using a proxy, hence I can't just use dynamic SQL in a single step.

0

1 Answer 1

1

The most direct way around this issue is to separate the stages of the job into two jobs - one job that decides what work needs to be done, and a 2nd job that is updated with the definition of the work. The first job responds to the alert or schedule and updates the definition of steps in the 2nd job, then fires up that 2nd job to do the actual work.

Getting away from the rigging for a SQL Server agent job for a minute, consider two jobs:

  1. Test self-updating job - part 1
  2. Test self-updating job - part 2

Step 1 of the first job has the following T-SQL:

USE [msdb];
GO
DECLARE @job_name sysname = N'Test self-updating job - part 2';
DECLARE @job_id uniqueidentifier;

SELECT 
    @job_id = [sj].[job_id]
FROM [msdb].[dbo].[sysjobs] [sj]
WHERE [sj].[name] = @job_name;


DECLARE @command nvarchar(max);
IF EXISTS
(
    SELECT 1
    FROM [msdb].[dbo].[sysjobsteps] [sjs]
        INNER JOIN [msdb].[dbo].[sysjobs] [sj] ON [sjs].[job_id] = [sj].[job_id]
    WHERE [sj].[name] = N'Test self-updating job - part 2'
        AND [sjs].[step_id] = 1
        AND [sjs].[command] = N'PRINT N''A'';'
)
BEGIN
    SET @command = N'PRINT N''B'';'; 
END
ELSE
BEGIN
    SET @command = N'PRINT N''A'';'; 
END;

EXEC [msdb].[dbo].[sp_update_jobstep] @job_name = @job_name, @step_id = 1, @command = @command;
EXEC [msdb].[dbo].[sp_start_job] @job_name = @job_name, @step_name = N'step 1';

Job 2 has this as the initial content of step 1:

PRINT N'A';

When the first job runs, it switches the contents of the command in job 2, before starting it with sp_start_job.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.