T-SQL创建SQLServer作业
-- ============================================= -- Author:Czhipu -- CreateDate:2014-10-15 -- Description:<Description, 竞猜作业> -- ============================================= -- 新建作业分类 Mall_MONITORING EXEC msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Mall_MONITORING' ; GO -- 删除作业分类 EXEC msdb.dbo.sp_delete_category @name = N'Mall_MONITORING', @class = N'JOB' ; GO -- 修改作业分类 EXEC msdb.dbo.sp_update_category @class = N'JOB', @name = N'Mall_MONITORING1', @new_name = N'Mall_MONITORING' ; GO -- 查看作业分类 SELECT * FROM msdb.dbo.syscategories WHERE NAME='Mall_MONITORING' -- 新建作业 /* 1. 执行 sp_add_job 来创建作业。 2. 执行 sp_add_jobstep 来创建一个或多个作业步骤。 3. 执行 sp_add_schedule 来创建计划。 4. 执行 sp_attach_schedule 将计划附加到作业。 5. 执行 sp_add_jobserver 来设置作业的服务器。 */ USE [TestDB] GO SELECT * FROM msdb.dbo.sysjobs_view /****** Object: Job [JOB_CLEAR_GUESS] Script Date: 10/15/2014 15:25:09 ******/ IF EXISTS(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'JOB_CLEAR_GUESS') EXEC msdb.dbo.sp_delete_job @job_id=N'a5dff08b-95f8-498e-a6c9-59241fe197b4', @delete_unused_schedule=1 GO USE [msdb] GO /****** Object: Job [JOB_CLEAR_GUESS] Script Date: 10/15/2014 15:25:09 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: Job [JOB_CLEAR_GUESS] Script Date: 10/15/2014 15:25:09 ******/ IF NOT EXISTS(SELECT name FROM msdb.dbo.syscategories WHERE name=N'Mall_MONITORING' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Mall_MONITORING' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode =msdb.dbo.sp_add_job @job_name=N'JOB_CLEAR_GUESS', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'每天执行EXEC JOB_CLEAR_GUESS 实现结算竞猜。', @category_name=N'Mall_MONITORING', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Step 1: recycle the errorlog] Script Date: 10/15/2014 15:25:09 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1: recycle the errorlog', @step_id=1, @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'TSQL', @command=N'exec msdb.dbo.sp_cycle_errorlog', @database_name=N'msdb', @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=N'Job Schedule', @enabled=1, @freq_type=4, --指示作业执行时间的值(1:一次;4:每天;8:每周;16:每月) @freq_interval=1, --执行作业的日期(1:一次;4:每天;8:每周;16:每月) @freq_subday_type=1, --指定 frequency_subday_interval 的单位(4:分钟;8:小时) @freq_subday_interval=0, --两次执行作业之间间隔的 frequency_subday_type 周期数 @freq_relative_interval=0, @freq_recurrence_factor=0, --作业执行计划之间相隔的周数或月数 @active_start_date=20130823, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_uid=N'2099c694-cd26-4edf-8803-179227bf8770' 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 -- 启动作业 EXEC msdb.dbo.sp_start_job @job_name='JOB_CLEAR_GUESS' -- 停止作业 EXEC msdb.dbo.sp_stop_job @job_name='JOB_CLEAR_GUESS' -- 启用或禁用作业 EXEC msdb.dbo.sp_update_job @job_name = N'JOB_CLEAR_GUESS', @enabled = 0 ; --0 禁用作业、 1启用作业 GO -- 删除作业 EXEC msdb.dbo.sp_delete_job @job_name = 'JOB_CLEAR_GUESS';
计较少了,快乐就多了。