SQL Server作业的同步(二)
ob @job_name=N'''+@jobname+''','
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @enabled='+RTRIM(@isenable)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @notify_level_eventlog='+RTRIM(@EventLogLevel)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @notify_level_email='+RTRIM(@EmailLevel)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @notify_level_netsend='+RTRIM(@NetSendLevel)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @notify_level_page='+RTRIM(@PageLevel)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @notify_email_operator_name ='''+RTRIM(@EmailLeveloprid)+''', ' www.2cto.com
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @notify_netsend_operator_name='''+RTRIM(@NetSendLeveloprid)+''', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @notify_page_operator_name='''+RTRIM(@PageLeveloprid)+''', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @delete_level='+RTRIM(@delete_level)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @description=N'''+@description+''', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @category_name=N'''+@category_name+''', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @owner_login_name=N'''+@owner_log_name+''', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @job_id = @jobId OUTPUT'
SET @retrun = @retrun+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
--SELECT * FROM msdb.dbo.syscategories
DECLARE @step_id INT
declare @step_name nvarchar(512) ,@cmdexec_success_code INT,@on_success_action INT,@on_success_step_id INT
,@on_fail_action INT,@on_fail_step_id INT,@retry_attempts INT,@retry_interval INT,@os_run_priority INT
,@subsystem NVARCHAR(512),@database_name NVARCHAR(512),@flags INT,@command NVARCHAR(max)
DECLARE jbcur CURSOR FOR SELECT step_id FROM msdb..sysjobsteps WHERE job_id = @jobid ORDER BY step_id ;
OPEN jbcur;
FETCH NEXT FROM jbcur INTO @step_id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @step_name = step_name
,@cmdexec_success_code= cmdexec_success_code
,@on_success_action = on_success_action
,@on_success_step_id = on_success_step_id
,@on_fail_action = on_fail_action
,@on_fail_step_id = on_fail_step_id
,@retry_attempts = retry_attempts
,@retry_interval = retry_interval
,@os_run_priority = os_run_priority
,@subsystem = subsystem
,@database_name = database_name
,@command = command
,@flags = flags
FROM msdb..sysjobsteps a WHERE job_id = @jobid and step_id = @step_id
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @step_name=N'''+@step_name+''', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @step_id='+RTRIM(@step_id)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @cmdexec_success_code='+RTRIM(@cmdexec_success_code)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @on_success_action='+RTRIM(@on_success_action)+', '
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @on_success_step_id='+RTRIM(@on_success_step_id)+', ' www.2cto.com
SET @retrun = @retrun+CHAR(13)+CHAR(10) + ' @on_fail_action='+RTRIM(@on_fail_action)+', '