USP_BUSINESSPROCESSJOBSCHEDULE_EDIT

Edits a business process job schedule.

Parameters

Parameter	Parameter Type	Mode
@SCHEDULEID	int	IN
@COMMAND	nvarchar(4000)	IN
@ENABLESCHEDULE	bit	IN
@FREQENCYTYPE	int	IN
@FREQUENCYINTERVAL	int	IN
@FREQUENCYSUBDAYTYPE	int	IN
@FREQUENCYSUBDAYINTERVAL	int	IN
@FREQUENCYRELATIVEINTERVAL	int	IN
@FREQUENCYRECURRENCEFACTOR	int	IN
@ACTIVESTARTDATE	char(8)	IN
@ACTIVEENDDATE	char(8)	IN
@ACTIVESTARTTIME	char(6)	IN
@ACTIVEENDTIME	char(6)	IN

Definition

Copy



            CREATE procedure dbo.USP_BUSINESSPROCESSJOBSCHEDULE_EDIT(@SCHEDULEID int,
                @COMMAND nvarchar(4000),
                @ENABLESCHEDULE bit = 1,
                @FREQENCYTYPE int = 0,
                @FREQUENCYINTERVAL int = 1,
                @FREQUENCYSUBDAYTYPE int = 0,
                @FREQUENCYSUBDAYINTERVAL int = 0,
                @FREQUENCYRELATIVEINTERVAL int = 0,
                @FREQUENCYRECURRENCEFACTOR int = 0,
                @ACTIVESTARTDATE char(8) = null,
                @ACTIVEENDDATE char(8) = null,
                @ACTIVESTARTTIME char(6) = null,
                @ACTIVEENDTIME char(6) = null)
            as

                BEGIN TRANSACTION
                DECLARE @ReturnCode INT
                SELECT @ReturnCode = 0

                EXEC @ReturnCode = msdb.dbo.sp_update_schedule @schedule_id=@SCHEDULEID,
                        @enabled=@ENABLESCHEDULE, 
                        @freq_type=@FREQENCYTYPE, 
                        @freq_interval=@FREQUENCYINTERVAL, 
                        @freq_subday_type=@FREQUENCYSUBDAYTYPE, 
                        @freq_subday_interval=@FREQUENCYSUBDAYINTERVAL, 
                        @freq_relative_interval=@FREQUENCYRELATIVEINTERVAL, 
                        @freq_recurrence_factor=@FREQUENCYRECURRENCEFACTOR, 
                        @active_start_date=@ACTIVESTARTDATE, 
                        @active_end_date=@ACTIVEENDDATE, 
                        @active_start_time=@ACTIVESTARTTIME, 
                        @active_end_time=@ACTIVEENDTIME
                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

                declare @JOBID uniqueidentifier;
                declare @STEPID int;

                select @JOBID = sysjobsteps.job_id,
                    @STEPID = sysjobsteps.step_id
                from msdb.dbo.sysjobsteps
                inner join msdb.dbo.sysjobschedules on sysjobsteps.job_id = sysjobschedules.job_id
                inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
                where sysschedules.schedule_id = @SCHEDULEID;

                exec @ReturnCode = msdb.dbo.sp_update_jobstep @job_id=@JOBID, @step_id=@STEPID, @command=@COMMAND;
                IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

                COMMIT TRANSACTION
                GOTO EndSave

                QuitWithRollback:
                    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

                EndSave: