SQLServer的job创建，修改，删除等

http://www.yesky.com/imagesnew/software/tsql/ts_sp_adda_5wyq.htm

 

EXEC dbo.TLZZZZAA00_SP_REG_JOB
@a_Fle_Ctrl_Cmd = 'CALL D:\PB1PD1\opt\script\file_chk.vbs BDPD003_BD1D022I110 BDZD001_BD1D022I110'
,@a_Ssis_Pkg_Nm = 'BDIW01AA00_PG_CMN_ACT_HIS_LOG.dtsx'
,@a_Mkn_FIL_Cmd = 'CALL D:\PB1PD1\opt\script\MKN_CTRL_FLE.vbs BD1D022I110_BD1D023D140.txt'
,@a_Err_Cmd = 'CALL D:\PB1PD1\opt\script\sqlAgentError.bat'
,@a_Job_Nm = 'BD1D022I110_共通行動履歴ログ取込・更新'
,@a_Ssis_Pkg_Stp_Nm = '共通行動履歴ログ取込・更新'

/****** Object: StoredProcedure [dbo].[TLZZZZAA00_SP_REG_JOB] Script Date: 2016/06/21 11:14:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
* =====================================================================
* 機能名 : SQLAgent作成ジョブ
* 結合テスト環境へジョブを一括登録するためのプロシージャ
* 汎用的ではないため、使用する際は注意
* 機能ID : CREATE_SQLAGENT_JOB
* 引 数 : 1.ファイル監視
* 引 数 : 2.SSIS PKG名
* 引 数 : 3.ファイル作成
* 引 数 : 4.エラーハンドリング
* 引 数 : 5.ジョブ名
* 引 数 : 6.SSIS PKGステップ名
* 戻り値 : ステータス 0:正常、100:異常
* メッセージ
* =====================================================================
*/
-- TLZZZZAA00_SP_LCSTM_NO_UPD
CREATE PROCEDURE [dbo].[TLZZZZAA00_SP_REG_JOB]
@a_Fle_Ctrl_Cmd NVARCHAR(3000)
,@a_Ssis_Pkg_Nm NVARCHAR(3000)
,@a_Mkn_FIL_Cmd NVARCHAR(3000)
,@a_Err_Cmd NVARCHAR(3000)
,@a_Job_Nm NVARCHAR(3000)
,@a_Ssis_Pkg_Stp_Nm NVARCHAR(3000)

AS

BEGIN TRANSACTION
DECLARE @l_Ssis_cmd NVARCHAR(3000);
SET @l_Ssis_cmd = N'/ISSERVER "\"\SSISDB\B-DashProject\B-DashProject\'+@a_Ssis_Pkg_Nm+'\"" /SERVER "\"PBDB902V\PBDB901\"" /ENVREFERENCE 9 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@a_Job_Nm,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'使用できる説明はありません。',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'PBDB902V\batuser', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [ファイル監視] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ファイル監視',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=4,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=@a_Fle_Ctrl_Cmd,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [SSIS実行] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@a_Ssis_Pkg_Stp_Nm,
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=4,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=@l_Ssis_cmd,
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [ファイル作成] Script Date: 2016/02/17 10:03:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ファイル作成',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=4,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=@a_Mkn_FIL_Cmd,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [エラーハンドリング] Script Date: 2016/02/17 10:03:52 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'エラーハンドリング',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=@a_Err_Cmd,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@a_Ssis_Pkg_Stp_Nm,
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160217,
@active_end_date=99991231,
@active_start_time=100500,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO