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';

 

转载于:https://www.cnblogs.com/Czhipu/p/4831476.html