December 22, 2008 at 11:11 am
I'm trying to modify a sql agent job on MSDE (SQL 2000)
USE [msdb]
GO
DECLARE @JobID binary(16)
SELECT @JobID = CAST('49b604e1-259a-4d98-b686-2f018950c803' AS uniqueidentifier)
EXECUTE msdb.dbo.sp_update_jobstep @job_id = @JobID, @step_id = 1,
@retry_attempts = 1,
@on_fail_action = 4
EXECUTE msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Notify On Fail',
@command = N'EXEC dbPlantPerf.dbo.NotifyJobCompleted
@status = ''Failed''
, @NotificationName = ''Linepack Calculation Failed''
, @JobName = ''Compute Latest Linepack''',
@database_name = N'dbLinepack',
@subsystem = N'TSQL',
@flags = 6,
@retry_attempts = 0,
@retry_interval = 0,
@on_success_step_id = 0,
@on_success_action = 3,
@on_fail_step_id = 0,
@on_fail_action = 2
GO
The script fails with this error
SQL Server Database Error: The specified '@on_failure_step' is invalid (valid values are greater than 0 but excluding 1)
I can't find any documentation for the parameter @on_failure_step.
Has anybody seen this or see another problem with the script?
January 29, 2009 at 9:44 am
hope you figured this out by now but the error typically occurs when you have @on_failure_Action set to 4 and @on_Failure_step set 0. It needs to be an actual step ID if @On_failure Action is 4.
Looks like you are doing this with the update step proc. As I stated above you will want to also specify the Step ID
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply