Changing schedules for a Job outside SQL Server

[teddysnips]

Below is the script of a Job called "eFIMS_SendEmai l" that I wish to
run. The intention is that every day of the week the job will execute
a SPROC at timed intervals. For example, the SundayRun schedule will
run once every 1 hours from 00:30:00 to 23:59:59

However, the clients have stated that they want an interface to this to
enable them easily to change the start time and frequency interval for
each day. It's an easy matter for me to paint them a form from within
the target application to enable the user to enter the start time and
interval for each day. I can then pass these as parameters to a SPROC.
How can I use these values to change the schedules for the job? For
example, if wanted to change SundayRun from once every 1 hours to once
every 30 mins? I know I could do it the hard way, by using string
manipulation (e.g. find string 'SundayRun', then look for the next
occurrence of @active_start_t ime, @freq_subday_ty pe,
@freq_subday_in terval etc. and do some replacement) but this seems
somewhat tricky.

Many thanks

Edward

-- Script generated on 8/31/2006 9:27 AM
-- By: sa
-- Server: BISMARK

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscat egories WHERE name =
N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add _category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjob s
WHERE (name = N'eFIMS_SendEma il')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjob servers
WHERE (job_id = @JobID) AND (server_id <0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''eFIMS_SendEma il'' since there
is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollbac k
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_del ete_job @job_name = N'eFIMS_SendEma il'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add _job @job_id = @JobID OUTPUT ,
@job_name = N'eFIMS_SendEma il', @owner_login_na me = N'sa', @description
= N'No description available.', @category_name = N'[Uncategorized
(Local)]', @enabled = 0, @notify_level_e mail = 0, @notify_level_p age =
0, @notify_level_n etsend = 0, @notify_level_e ventlog = 2,
@delete_level= 0
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add _jobstep @job_id = @JobID,
@step_id = 1, @step_name = N'SendEmail', @command = N'EXEC
stpSendEmailCon firmation', @database_name = N'194-eFIMS', @server =
N'', @database_user_ name = N'', @subsystem = N'TSQL',
@cmdexec_succes s_code = 0, @flags = 4, @retry_attempts = 0,
@retry_interval = 1, @output_file_na me = N'', @on_success_ste p_id = 0,
@on_success_act ion = 1, @on_fail_step_i d = 0, @on_fail_action = 2
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k
EXECUTE @ReturnCode = msdb.dbo.sp_upd ate_job @job_id = @JobID,
@start_step_id = 1

IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add _jobschedule @job_id = @JobID,
@name = N'SundayRun', @enabled = 1, @freq_type = 8, @active_start_d ate
= 20060831, @active_start_t ime = 3000, @freq_interval = 1,
@freq_subday_ty pe = 8, @freq_subday_in terval = 1,
@freq_relative_ interval = 0, @freq_recurrenc e_factor = 1,
@active_end_dat e = 99991231, @active_end_tim e = 235959
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k
EXECUTE @ReturnCode = msdb.dbo.sp_add _jobschedule @job_id = @JobID,
@name = N'MondayRun', @enabled = 1, @freq_type = 8, @active_start_d ate
= 20060831, @active_start_t ime = 3000, @freq_interval = 2,
@freq_subday_ty pe = 4, @freq_subday_in terval = 30,
@freq_relative_ interval = 0, @freq_recurrenc e_factor = 1,
@active_end_dat e = 99991231, @active_end_tim e = 235959
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k
EXECUTE @ReturnCode = msdb.dbo.sp_add _jobschedule @job_id = @JobID,
@name = N'TuesdayRun', @enabled = 1, @freq_type = 8, @active_start_d ate
= 20060831, @active_start_t ime = 4000, @freq_interval = 4,
@freq_subday_ty pe = 4, @freq_subday_in terval = 40,
@freq_relative_ interval = 0, @freq_recurrenc e_factor = 1,
@active_end_dat e = 99991231, @active_end_tim e = 235959
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k
EXECUTE @ReturnCode = msdb.dbo.sp_add _jobschedule @job_id = @JobID,
@name = N'WednesdayRun' , @enabled = 1, @freq_type = 8,
@active_start_d ate = 20060830, @active_start_t ime = 3000,
@freq_interval = 8, @freq_subday_ty pe = 4, @freq_subday_in terval = 30,
@freq_relative_ interval = 0, @freq_recurrenc e_factor = 1,
@active_end_dat e = 99991231, @active_end_tim e = 235959
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k
EXECUTE @ReturnCode = msdb.dbo.sp_add _jobschedule @job_id = @JobID,
@name = N'ThursdayRun', @enabled = 1, @freq_type = 8,
@active_start_d ate = 20060831, @active_start_t ime = 3500,
@freq_interval = 16, @freq_subday_ty pe = 4, @freq_subday_in terval = 35,
@freq_relative_ interval = 0, @freq_recurrenc e_factor = 1,
@active_end_dat e = 99991231, @active_end_tim e = 235959
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k
EXECUTE @ReturnCode = msdb.dbo.sp_add _jobschedule @job_id = @JobID,
@name = N'FridayRun', @enabled = 1, @freq_type = 8, @active_start_d ate
= 20060831, @active_start_t ime = 3000, @freq_interval = 32,
@freq_subday_ty pe = 4, @freq_subday_in terval = 30,
@freq_relative_ interval = 0, @freq_recurrenc e_factor = 1,
@active_end_dat e = 99991231, @active_end_tim e = 235959
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k
EXECUTE @ReturnCode = msdb.dbo.sp_add _jobschedule @job_id = @JobID,
@name = N'SaturdayRun', @enabled = 1, @freq_type = 8,
@active_start_d ate = 20060831, @active_start_t ime = 0, @freq_interval =
64, @freq_subday_ty pe = 8, @freq_subday_in terval = 1,
@freq_relative_ interval = 0, @freq_recurrenc e_factor = 1,
@active_end_dat e = 99991231, @active_end_tim e = 235959
IF (@@ERROR <0 OR @ReturnCode <0) GOTO QuitWithRollbac k

-- 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 QuitWithRollbac k

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollbac k:
IF (@@TRANCOUNT 0) ROLLBACK TRANSACTION
EndSave:

[teddysnips]

te********@hotm ail.com wrote:

Repeat after me - "Look in BOL before posting; Look in BOL before
posting"

- sorry to waste all your time!

Edward