if job is running... turn off step...
-
need another opinion. does this look right?
i'm trying to setup a job step which looks at a value
in a table, and based on that value will execute a stored
procedure.
-----------------------------------
use msdb
go
if [run_status] = '4'
where
(select sysjobs.name
from sysjobs join sysjobhistory on (sysjobs.job_id = sysjobhistory.job_id)
where sysjobs.name = 'MYJOB')
then
sp_update_jobstep
@job_name = 'MYJOB',
@step_id = 3,
@on_success_action = 1
else
sp_update_jobstep
@job_name = 'MYJOB',
@step_id = 3,
@on_success_action = 3
end if
-----------------------------------
_________________________
-
I think your if statement doesn't make any sense to me syntactically... use the following...
if
(select sysjobhistory.status
from sysjobs join sysjobhistory on (sysjobs.job_id = sysjobhistory.job_id)
where sysjobs.name = 'MYJOB') = 4
Note: Your update will not effect the current job execution, it will effect for next execution...
MohammedU
Microsoft SQL Server MVP -
what about the then/else part? any thoughts on that?
_________________________
-
Then else part is fine but as I mentioned your current execution of the job will not be effected...
MohammedU
Microsoft SQL Server MVP -
1) keep Mohammed Uddin's note in mind : you cannot influence the running job ! (except stopping it)
2) there is no then nor end if in TSQL if-statement.
use msdb
go
declare @myrun_status int
select @myrun_status = [run_status]
from sysjobs
inner join sysjobhistory
on sysjobs.job_id = sysjobhistory.job_id
where sysjobs.name = 'MYJOB'
if @myrun_status = '4'
begin
sp_update_jobstep @job_name = 'MYJOB', @step_id = 3,@on_success_action = 1
end
else
begin
sp_update_jobstep @job_name = 'MYJOB', @step_id = 3,@on_success_action = 3
end
3) I have some jobs with a flip-flop mechanism.
A start step is set and multiple steps have "quit with success".
the trick is setting the start step.
The first time the job is launched, certain steps run, the last step from the first sequence sets the start-step of the next run.
With the next run, the last step sets the start-step for the following run. In my case this job only has a two-run scenario, but you can build ......
A step is only run with one job-run-sequence, or else you have to alter then "next step" for jobstep during your run, making it more complex.
KISS is nice guideline.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
just as a litle example :
USE
[msdb]
GO
/****** Object: Job [FlipFlop] Script Date: 12/21/2006 09:44:50 ******/
BEGIN
TRANSACTION
DECLARE
@ReturnCode INT
SELECT
@ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 12/21/2006 09:44:50 ******/
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'FlipFlop',
@enabled
=0,
@notify_level_eventlog
=2,
@notify_level_email
=0,
@notify_level_netsend
=0,
@notify_level_page
=0,
@delete_level
=0,
@description
=N'Stop/Start Communication',
@category_name
=N'[Uncategorized (Local)]',
@owner_login_name
=N'sa', @job_id = @jobId OUTPUT
IF
(@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable_Communication_Jobs] Script Date: 12/21/2006 09:44:50 ******/
EXEC
@ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable_Communication_Jobs',
@step_id
=1,
@cmdexec_success_code
=0,
@on_success_action
=3,
@on_success_step_id
=0,
@on_fail_action
=2,
@on_fail_step_id
=0,
@retry_attempts
=0,
@retry_interval
=1,
@os_run_priority
=0, @subsystem=N'TSQL',
@command
=N'DECLARE @rc int
DECLARE @NameLike varchar(120)
DECLARE @DisabledPrefix varchar(50)
DECLARE @tsStop datetime
DECLARE @AlertsDone int
DECLARE @JobsDone int
Declare @StopStart varchar(20)
-- Set parameter values
select @StopStart = upper(''stop'')
, @NameLike = ''DB2''
, @DisabledPrefix = ''Communication_Disabled_''
if @StopStart like ''%STOP%''
begin
EXEC @rc = [msdb].[dbo].[Spc_ALZDBA_Disable_Jobs] @NameLike, @DisabledPrefix, @tsStop OUTPUT , @AlertsDone OUTPUT , @JobsDone OUTPUT
print ''RC Disable_proc ['' + convert(varchar(25),@RC) + ''] - TsStop ['' + convert(varchar(25),@tsStop,121) + ''] - AlertsDone ['' + convert(varchar(25),@AlertsDone,121) + ''] - JobsDone ['' + convert(varchar(25),@JobsDone,121) + '']''
end'
,
@database_name
=N'msdb',
@flags
=0
IF
(@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Set Startup step to Enable_Communication_Jobs] Script Date: 12/21/2006 09:44:51 ******/
EXEC
@ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Set Startup step to Enable_Communication_Jobs',
@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
=1,
@os_run_priority
=0, @subsystem=N'TSQL',
@command
=N'EXECUTE msdb.dbo.sp_update_job @job_name = ''ALZ_StopStart_Communication'' , @start_step_id = 3
'
,
@database_name
=N'msdb',
@flags
=0
IF
(@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Enable_Communication_Jobs] Script Date: 12/21/2006 09:44:51 ******/
EXEC
@ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Enable_Communication_Jobs',
@step_id
=3,
@cmdexec_success_code
=0,
@on_success_action
=3,
@on_success_step_id
=0,
@on_fail_action
=2,
@on_fail_step_id
=0,
@retry_attempts
=0,
@retry_interval
=1,
@os_run_priority
=0, @subsystem=N'TSQL',
@command
=N'DECLARE @rc int
DECLARE @NameLike varchar(120)
DECLARE @DisabledPrefix varchar(50)
DECLARE @tsStop datetime
DECLARE @AlertsDone int
DECLARE @JobsDone int
Declare @StopStart varchar(20)
-- Set parameter values
select @StopStart = upper(''start'')
, @NameLike = ''DB2''
, @DisabledPrefix = ''Communication_Disabled_''
DECLARE @tsStopped datetime
Declare @NamePrefix varchar(120)
if @DisabledPrefix = ''ALZDBADisabled_''
begin
declare @userid varchar(100)
set @userid = suser_sname()
set @NamePrefix = substring(@UserId, CHARINDEX ( ''\'' , @userid, 1) + 1 , datalength(@UserId)) + ''_Disabled_''
end
else
begin
set @NamePrefix = @DisabledPrefix
end
if @tsStop is null
begin
set @tsStopped = convert(datetime, convert(char(10), getdate(),121))
end
else
begin
set @tsStopped = @tsStop
end
print @NamePrefix + '' - tsStopped ['' + convert(varchar(25),@tsStopped,121) + '']''
-- Set parameter values
EXEC @rc = [msdb].[dbo].[Spc_ALZDBA_Enable_Jobs] @NamePrefix, @tsStopped, @AlertsDone OUTPUT , @JobsDone OUTPUT
print ''RC Enable_proc ['' + convert(varchar(25),@RC) + ''] - AlertsDone ['' + convert(varchar(25),@AlertsDone,121) + ''] - JobsDone ['' + convert(varchar(25),@JobsDone,121) + '']''
END
'
,
@database_name
=N'msdb',
@flags
=0
IF
(@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Set Startup step to Disable_Communication_Jobs] Script Date: 12/21/2006 09:44:51 ******/
EXEC
@ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Set Startup step to Disable_Communication_Jobs',
@step_id
=4,
@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
=1,
@os_run_priority
=0, @subsystem=N'TSQL',
@command
=N'EXECUTE msdb.dbo.sp_update_job @job_name = ''ALZ_StopStart_CommunicatieMF'' , @start_step_id = 1',
@database_name
=N'msdb',
@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_jobschedule @job_id=@jobId, @name=N'CommunicatieMF',
@enabled
=0,
@freq_type
=1,
@freq_interval
=0,
@freq_subday_type
=0,
@freq_subday_interval
=0,
@freq_relative_interval
=0,
@freq_recurrence_factor
=0,
@active_start_date
=20041030,
@active_end_date
=99991231,
@active_start_time
=55900,
@active_end_time
=235959
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
:
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
WOW... thanks for all the good replies!!
i'm working through this now.
i really appreciate it!
_________________________
-
actually having some errors with this although the tsql looks
fine.
use msdb
go
declare @myrun_status int
select @myrun_status = [run_status]
from sysjobs
inner join sysjobhistory
on sysjobs.job_id = sysjobhistory.job_id
where sysjobs.name = 'MYJOB'
if @myrun_status = '4'
begin
sp_update_jobstep @job_name = 'MYJOB', @step_id = 3,@on_success_action = 1
end
else
begin
sp_update_jobstep @job_name = 'MYJOB', @step_id = 3,@on_success_action = 3
end
is (begin) needed in this case or rather (exec)
forgive me if this is a basic question.
_________________________
-
i got it. it's the simple things...
turns out after you have the (begin) all you need to do is prefix the
sp_update_jobstep with the following.
execute msdb.dbo.
and syntax checks out fine after that.
thanks again for all the replies people.
_________________________
-
Aways use exec to execute procedures and qualify the names too..
exec master.dbo.sp_help
MohammedU
Microsoft SQL Server MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply