SQL Server作业的同步(三)
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @on_fail_step_id='+RTRIM(@on_fail_step_id)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @retry_attempts='+RTRIM(@retry_attempts)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @retry_interval='+RTRIM(@retry_interval)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @os_run_priority='+RTRIM(@os_run_priority)+', @subsystem=N'''+@subsystem+''', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @database_name=N'''+@database_name+''','
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @flags='+RTRIM(@flags)+' ,'
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @command=N'''+REPLACE(@command,'''','''''')+''''
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
FETCH NEXT FROM jbcur INTO @step_id
END
CLOSE jbcur
DEALLOCATE jbcur
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = '+rtrim(@start_step_id)
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback '
DECLARE @enabled INT,@freq_type INT,@freq_interval INT,@freq_subday_type INT,@freq_subday_interval INT
,@freq_relative_interval INT,@freq_recurrence_factor INT,@active_start_date INT,@active_end_date INT
,@active_start_time INT,@active_end_time INT,@name VARCHAR(512)
SELECT
@name = a.name
,@enabled = enabled
,@freq_interval = freq_interval
,@freq_type = freq_type
,@freq_subday_type=freq_subday_type
,@freq_relative_interval=freq_relative_interval
,@freq_recurrence_factor=freq_recurrence_factor
,@active_start_date=active_start_date
,@active_end_date=active_end_date
,@active_start_time=active_start_time
,@active_end_time=active_end_time
FROM msdb..sysschedules a
INNER JOIN msdb.dbo.sysjobschedules b ON a.schedule_id = b.schedule_id
WHERE job_id = @jobId
IF(@name IS not null)
begin www.2cto.com
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'''+@name+''', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @enabled='+RTRIM(@enabled)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @freq_type='+RTRIM(@freq_type)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @freq_interval='+RTRIM(@freq_interval)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @freq_subday_type='+RTRIM(@freq_subday_type)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @freq_subday_interval='+RTRIM(@freq_subday_interval)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @freq_relative_interval='+RTRIM(@freq_relative_interval)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @freq_recurrence_factor='+RTRIM(@freq_recurrence_factor)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @active_start_date='+RTRIM(@active_start_date)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @active_end_date='+RTRIM(@active_end_date)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @active_start_time='+RTRIM(@active_start_time)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) +