April 3, 2008 at 9:56 pm
I have created an SQL Agent job under SQL 2005.
The job has two schedules.
The first schedule is defined to occur Monday thru Friday at 10pm
The second schedule is defined to occur on Sunday at 6am.
There are two job steps.
When the job is initiated by the first schedule, it starts at step 1, then proceeds to step 2.
Ideally when the job is initiated by the second schedule, I want it to start at step2 instead.
I do NOT want to create another Complete agent job
Any ideas on how I might achieve this?
One thought was to have a new step 1 that some how determines which schedule initiated the job and let it determine which step should occur next.
However I have been unable to work out how I can control which step should occur next from within the job step itself.
Knowing the schedule can be simply achieved by using GETDATE() within the new first job since the current start times are very different.
------
Robert
April 4, 2008 at 12:09 am
- afaik you cannot directly query wich schedule started your current job.
- I've use a "flip/flop" mechanisme in a job I knew that runs one time starting from stepx, the other time staring from stepy
When starting from stepx, the first thing I do is set the starting step for the next execution to stepy and the other way arround.
Here's the actual job..
BEGIN TRANSACTION
declare @RunDatum int
declare @RunTijd int
select @RunDatum = 20041030
, @RunTijd = 55900
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Check the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'DBA_StopStart_CommunicationMF')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''DBA_StopStart_CommunicationMF'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
begin
-- Delete the [local] job
-- EXECUTE msdb.dbo.sp_delete_job @job_name = N'DBA_StopStart_CommunicationMF'
-- SELECT @JobID = NULL
-- niet deleten, maar aanpassen schedule !
EXECUTE @ReturnCode = msdb.dbo.sp_update_jobschedule @job_id = @JobID, @name = N'CommunicatieMF', @enabled = 1, @freq_type = 1, @active_start_date = @RunDatum, @active_start_time = @RunTijd
print 'Jobschedule aangepast - datum = [' + cast( @RunDatum as varchar(10)) + '] tijd [' + cast( @RunTijd as varchar(10)) + ']'
end
END
-- enkel indien de job nog niet bestaat installeren
if @JobID is NULL
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'DBA_StopStart_CommunicationMF', @owner_login_name = N'sa', @description = N'Stop/Start CommunicatieMF DB2 SQLServer', @category_name = N'[Uncategorized (Local)]', @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'Disable_Communication_Jobs', @command = N'DECLARE @rc int
DECLARE @HotRunNY char(1)
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 @HotRunNY = ''Y''
, @StopStart = upper(''stop'')
, @NameLike = ''DB2''
, @DisabledPrefix = ''CommunicatieMF_Disabled_''
if @StopStart like ''%STOP%''
begin
EXEC @rc = [msdb].[dbo].[Spc_DBA_Disable_Jobs] @HotRunNY, @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
if @StopStart like ''%START%''
begin
DECLARE @tsStopped datetime
Declare @NamePrefix varchar(120)
if @DisabledPrefix = ''ALZDisabled_''
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_DBA_Enable_Jobs] @HotRunNY, @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
--select * from msdb.dbo.T_ALZ_Disabled_Alerts', @database_name = N'msdb', @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 = 3, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Set Startup step to Enable_Communication_Jobs', @command = N'EXECUTE msdb.dbo.sp_update_job @job_name = ''DBA_StopStart_CommunicationMF'' , @start_step_id = 3
', @database_name = N'msdb', @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 = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'Enable_Communication_Jobs', @command = N'DECLARE @rc int
DECLARE @HotRunNY char(1)
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 @HotRunNY = ''Y''
, @StopStart = upper(''start'')
, @NameLike = ''DB2''
, @DisabledPrefix = ''CommunicatieMF_Disabled_''
if @StopStart like ''%STOP%''
begin
EXEC @rc = [msdb].[dbo].[Spc_DBA_Disable_Jobs] @HotRunNY, @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
if @StopStart like ''%START%''
begin
DECLARE @tsStopped datetime
Declare @NamePrefix varchar(120)
if @DisabledPrefix = ''ALZDisabled_''
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_DBA_Enable_Jobs] @HotRunNY, @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
--select * from msdb.dbo.T_ALZ_Disabled_Alerts', @database_name = N'msdb', @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 = 3, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 4, @step_name = N'Set Startup step to Disable_Communication_Jobs', @command = N'EXECUTE msdb.dbo.sp_update_job @job_name = ''DBA_StopStart_CommunicationMF'' , @start_step_id = 1', @database_name = N'msdb', @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 = 1, @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 = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
-- EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'CommunicatieMF', @enabled = 1, @freq_type = 1, @active_start_date = 20040911, @active_start_time = 55900
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'CommunicatieMF', @enabled = 1, @freq_type = 1, @active_start_date = @RunDatum, @active_start_time = @RunTijd
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:
print 'fout'
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
April 4, 2008 at 12:59 am
Thanks ALZDBA for the method.
I am going to define a new step 3 which will then change what the starting step will be for the job on its next execution, based on which day of the week the current running job was initiated.
------
Robert
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply