Job Notification store procedure help

Hi
 The following process is suppose to creates two sql server jobs. it is failing.  It gives the following error and creates only one job
The first error I am getting is
Warning: Non-existent step referenced by @on_fail_step_id.
Warning: Non-existent step referenced by @on_fail_step_id.


The secod error
only one job is created
**********************************************
 
BEGIN TRANSACTION            
  DECLARE       @JobID BINARY(16)  
  DECLARE       @ReturnCode INT    
  SELECT       @ReturnCode = 0    
  IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
  EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
  IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'Pubs FullBackup') > 0
  PRINT N'The job "Pubs FullBackup" already exists so will not be replaced.'
ELSE
BEGIN

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Pubs FullBackup', @owner_login_name = N'MAST_HODC\sqladm2003', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Tranlog Backup', @command = N'sqlmaint  -S B456-DBS0451\BUP4  -D Pubs  -BkUpLog G:\MSSQL8DataBUP4\Backup\  -BkUpMedia DISK  -CrBkSubDir  -VrfyBackup  -DelBkUps 3days  -BkExt "TRN"  -Rpt G:\MSSQL8DataBUP4\Backup\Pubs\Pubs_tranlog_backup.txt  -DelTxtRpt 3days', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 3, @on_fail_action = 4
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Database Backup', @command = N'sqlmaint  -S B456-DBS0451\BUP4  -D Pubs  -BkUpDB G:\MSSQL8DataBUP4\Backup\  -BkUpMedia DISK -CrBkSubDir  -VrfyBackup  -DelBkUps 3days  -Rpt G:\MSSQL8DataBUP4\Backup\Pubs\Pubs_database_backup.txt  -DelTxtRpt 3days', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 3, @on_fail_action = 4
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'email notification', @command = N'DECLARE @Body VARCHAR(4000)

--Build Body of the Email
SELECT @Body =
''Instance:  B456-DBS0451\BUP4
Database:  Pubs
Jobname:   Pubs FullBackup
Severity:  Notify ROPerson''

--Supply sproc parms of: From, To, Subject, Body
EXEC sp_send_cdosysmail
      ''aci@hotmail.com'',
      ''aci@hotmail.com'',
      ''Server: B456-DBS0451\BUP4; Jobname:  Pubs FullBackup'',
      @Body', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 2, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 2

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Daily at 1am', @enabled = 1, @freq_type = 8, @active_start_date = 20010724, @active_start_time = 211200, @freq_interval = 126, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

********************************************

Thanks
Start Free Trial