angelIII,
raisError ('Cannot reschedule for after 7:58 am',16,1,'Too Late to Reschedule') WITH SETERROR
I changed it in both places but it still allowed the time to be reset.
any other suggestions?
Main Topics
Browse All Topicssql 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
/*************************
/************** 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
select @chrNew_Active_Start_Time = left(@chrNew_Active_Start_
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,
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
did you change the 11,1 to 16,1 ?
anyhow, you might use the GOTO:
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')
GOTO Done
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
select @chrNew_Active_Start_Time = left(@chrNew_Active_Start_
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,
Done:
The code like that with commit rollback and the raise error will help I think in addition to angell' s comment.
You can find detailed explanation in the link below
--4 pages of explanation
http://www.eps-publication
*********************Code Begins*****************
CREATE PROCEDURE P1
AS
DECLARE @LocalTran tinyint
, @RetVal int
, @Error int
, @Rowcount int
SET @LocalTran = 1
IF @@TRANCOUNT = 0 AND @LocalTran = 1
BEGIN
BEGIN TRANSACTION
END
EXEC @RetVal = P2
SELECT @Error = @@ERROR, @Rowcount = @@ROWCOUNT
IF @RetVal = -1 OR @Error > 0
BEGIN
IF @Error > 0
RAISERROR('P1: Call to P2 failed', 16,1)
GOTO ErrExit
END
<critical event>
SELECT @Error = @@ERROR, @Rowcount = @@ROWCOUNT
IF @Error > 0
BEGIN
RAISERROR ('P1: <critical event> failed', 16, 1)
GOTO ErrExit
END
-- Normal exit
IF @LocalTran = 1 AND @@TRANCOUNT > 0
COMMIT
RETURN(0)
-- Error Exit
ErrExit:
IF @LocalTran = 1 AND @@TRANCOUNT > 0
BEGIN
ROLLBACK
END
RETURN(-1)
*********************Code Ends*****************
Kosturdur
Business Accounts
Answer for Membership
by: angelIIIPosted on 2007-01-19 at 14:05:06ID: 18352966
raisError ('Cannot reschedule for after 7:58 am',16,1,'Too Late to Reschedule') WITH SETERROR