-- =============================================-- Author：Czhipu-- CreateDate：2014-10-15-- Description：<Description, 竞猜作业>-- =============================================-- 新建作业分类 Mall_MONITORINGEXEC 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=1GO USE [msdb]GO /****** Object: Job [JOB_CLEAR_GUESS] Script Date: 10/15/2014 15:25:09 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @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)BEGINEXEC @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 OUTPUTIF (@@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=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @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 QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave: 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';