sql svr 2k
I want the job step to stop immediately if either of the if statements are true.
I have the following step code:
declare @retval int
declare @job_name varchar(128)
declare @job_name_for_reschedule varchar(128)
declare @job_id uniqueidentifier
declare @schedule_name varchar(128)
select @job_name_for_reschedule = 'STG_DataPush_Stuff'
select @schedule_name = 'Reset After Failure'
EXECUTE @retval = sp_verify_job_identifiers '@job_name',
'@job_id',
@job_name_for_reschedule OUTPUT,
@job_id OUTPUT
--IF @retval <> 0 Raise an error
declare @intMins_To_Add int
declare @dtStart_Time smalldatetime
declare @intNew_Active_Start_Date int
declare @chrNew_Active_Start_Time varchar(19)
declare @intNew_Active_Start_Time int
select @intMins_To_Add = 10
select @dtStart_Time = dateadd(minute,@intMins_To
_Add,getda
te())
/*************************
**********
**********
**********
**********
**********
**********
**********
**********
*******/
/************** If either of the following 2 if statements are true, I want the step to fail and stop immediately. it seems to continue running *********/
/*************************
**********
**********
**********
**********
**********
**********
**********
**********
*******/
--Raise error if reschedule time is after 7:58am
if (SELECT DATEPART(hh, @dtStart_Time)) >= 8
begin
raisError ('Cannot reschedule for after 8:00 am',11,1,'Too Late to Reschedule')
end
if (SELECT DATEPART(hh, @dtStart_Time)) = 7
begin
if (SELECT DATEPART(mi, @dtStart_Time)) > 58
begin
raisError ('Cannot reschedule for after 7:58 am',11,1,'Too Late to Reschedule')
end
end
SELECT @intNew_Active_Start_Date = Convert(varchar(8), @dtStart_Time, 112)
select @chrNew_Active_Start_Time = convert(varchar(19), @dtStart_Time,120)
select @chrNew_Active_Start_Time = right(@chrNew_Active_Start
_Time, 8)
select @chrNew_Active_Start_Time = left(@chrNew_Active_Start_
Time, 2) + substring(@chrNew_Active_S
tart_Time,
4,2) + right(@chrNew_Active_Start
_Time, 2)
select @intNew_Active_Start_Time = @chrNew_Active_Start_Time
EXEC sp_update_jobschedule @job_name = @job_name_for_reschedule, @name = @schedule_name,
@Active_Start_Date = @intNew_Active_Start_Date,
@Active_Start_Time = @intNew_Active_Start_Time,
@enabled = 1