I am working with a script that creates a SQL Agent job in SQL Server 2014. The job's last 2 steps are "Complete with Success" and "Complete with Failure". The script always warns of non-existent steps.

Warning: Non-existent step referenced by @on_fail_step_id. Warning: Non-existent step referenced by @on_success_step_id. Warning: Non-existent step referenced by @on_fail_step_id.

SET ANSI_WARNINGS OFF

has no effect on these as they are, I imagine, not ANSI warnings.

Is there a way to suppress these?

Advanced tab for Complete with Success Advanced tab for Complete with Failure

  • Can you double check your job steps (each one)? Except last step set others if success, go to next step. Last one if success, quit reporting success. If any steps fail you may chose to go to next one or quit with failure. – SqlWorldWide Apr 5 '17 at 13:15
  • I need to set the job status in a tracking table, which is the reason for having the steps for success and failure. They fire a stored proc that sets a job status. – Metaphor Apr 5 '17 at 13:19
  • btw, the job deploys and executes just fine, I just want to avoid extra warning clutter. – Metaphor Apr 5 '17 at 13:20
  • What are the actions for success/failure of the last 2 steps? As per this article you cannot fix it and should be classified as a bug. – SqlWorldWide Apr 5 '17 at 13:34
  • They both call a stored proc that sets job status. One with parameter 1 the other with 2. – Metaphor Apr 5 '17 at 13:46
up vote 1 down vote accepted

I was able to reproduce the issue. Problem is step 1 is refering to steps that are not created yet. Following code will generate the same error you are getting.

USE [msdb]
GO


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'test333', 
        @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'domain\account', @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'GetDate', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=4, 
        @on_success_step_id=2, 
        @on_fail_action=4, 
        @on_fail_step_id=3, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'select getdate()', 
        @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'success', 
        @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 ''i am success''', 
        @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'failure', 
        @step_id=3, 
        @cmdexec_success_code=0, 
        @on_success_action=2, 
        @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 ''i am failure''', 
        @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

I commented out 4 lines on the 1st step. Added a section calling sp_update_jobstep. This code will not throw the error you are getting. I am only pasting modified code here.

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'GetDate', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        --@on_success_action=4, 
        --@on_success_step_id=2, 
        --@on_fail_action=4, 
        --@on_fail_step_id=3, 
        ........
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

Add this at the end of creating last step and before running sp_add_jobserver.

EXEC msdb.dbo.sp_update_jobstep @job_id=@jobid, @step_id=1 , 
        @on_success_action=4, 
        @on_success_step_id=2, 
        @on_fail_action=4, 
        @on_fail_step_id=3

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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