SqlServer代理作业

来源：互联网发布：php forreach 编辑：程序博客网时间：2024/05/17 06:24

最近一直在学习SqlServer 作业方面的知识，总结一下。

一：作业存在的库。

msdb use msdb

Msdb数据库是代理服务数据库，为其报警、任务调度和记录操作员的操作提供存储空间。

二：查看作业分类

EXEC msdb.dbo.sp_help_category;    SELECT  category_id            ,--作业类别ID         category_class         ,--类别中项目类型：1=作业2=警报 3=操作员         category_type          ,--类别中类型：=本地、=多服务器、=无         name                    --分类名称    from msdb.dbo.syscategories

三：作业常用的几个步骤：

EXEC msdb.dbo.sp_delete_job EXEC msdb.dbo.sp_add_job EXEC msdb.dbo.sp_add_jobstep EXEC msdb..sp_add_jobschedule EXEC msdb.dbo.sp_add_jobserver  EXEC msdb.dbo.sp_start_job

四：写一个简单的案例：

begin transaction--申明变量declare @ReturnCode INT--赋值select @ReturnCode = 0--如果不包含 if not exists  (select * 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)]'  --上一条语句是否有错   @ReturnCode 是否赋值     QuitWithRollback  回滚事务  if (@@ERROR<>0 or @ReturnCode<>0) GOTO  QuitWithRollback end  declare @jobId BINARY(16) --名字  第一步  sp_add_job EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Proc_SyncBaseData',    @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)]',        --登录名称  @jobId有返回值        @owner_login_name=N'sa', @job_id = @jobId OUTPUT        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback         --第二部   sp_add_jobstep        exec  @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Proc_SyncBaseData',  --作业名称        @step_id=1,        @cmdexec_success_code=0,        @on_success_action=3,        @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 Proc_SyncBaseData', --执行的作业        @database_name=N'TOPK_DATA',        @flags=0        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback         --处理基础数据        exec @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'处理基础数据',        @step_id=2,        @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'DECLARE @num INT=1                               @database_name=N'TOPK_DATA',   --数据库名称               @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'1',              @enabled=1,              @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=20151231,              @active_end_date=99991231,              @active_start_time=10000,              @active_end_time=235959,              @schedule_uid=N'81392edd-5c9d-4bf0-a306-4ff22a0920cf'           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 EndSaveQuitWithRollback:    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:

--删除作业     IF  EXISTS (SELECT JOB_ID FROM MSDB.DBO.SYSJOBS_VIEW WHERE NAME =N'作业名称')      EXECUTE MSDB.DBO.SP_DELETE_JOB @JOB_NAME=N'作业名称'             --定义创建作业     DECLARE @jobid uniqueidentifier     EXEC msdb.dbo.sp_add_job             @job_name = N'作业名称',             @job_id = @jobid OUTPUT            --定义作业步骤     DECLARE @sql nvarchar(400),@dbname sysname     SELECT  @dbname=DB_NAME(), --执行的数据库（当前）             @sql=N'作业步骤内容' --一般定义的是使用TSQL处理的作业,这里定义要执行的Transact-SQL语句     EXEC msdb.dbo.sp_add_jobstep             @job_id = @jobid,             @step_name = N'作业步骤名称',             @subsystem = 'TSQL', --步骤的类型,一般为TSQL             @database_name=@dbname,             @command = @sql            --创建调度(使用后面专门定义的几种作业调度模板)     EXEC msdb..sp_add_jobschedule             @job_id = @jobid,             @name = N'调度名称',             @freq_type=4,                --每天             @freq_interval=1,            --指定每多少天发生一次,这里是1天.             @freq_subday_type=0x8,       --重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次             @freq_subday_interval=1,     --重复周期数,这里每小时执行一次             @active_start_date = NULL,   --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD             @active_end_date = 99991231, --作业执行的停止日期,默认为99991231,格式为YYYYMMDD             @active_start_time = 00000, --作业执行的开始时间,格式为HHMMSS             @active_end_time = 235959    --作业执行的停止时间,格式为HHMMSS            --添加目标服务器     DECLARE @servername sysname     SET @servername=CONVERT(nvarchar(128),SERVERPROPERTY(N'ServerName'))--当前SQL实例     EXEC msdb.dbo.sp_add_jobserver              @job_id = @jobid,             @server_name = @servername             -----------------------------------------------------------------------------------------------     --调度模板定义 sp_add_jobschedule            --只执行一次的作业调度     EXEC msdb..sp_add_jobschedule             @job_id = @jobid,             @name = N'调度名称',             @freq_type=1,                --仅执行一次             @active_start_date = NULL,   --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD             @active_start_time = 00000   --作业执行的开始时间,格式为HHMMSS            --日调度     EXEC msdb..sp_add_jobschedule             @job_id = @jobid,             @name = N'调度名称',             @freq_type=4,                --每天             @freq_interval=1,            --指定每多少天发生一次,这里是1天.             @freq_subday_type=0x8,       --重复方式,(0x1=指定的时间;0x4=多少分钟;0x8=多少小时)执行一次             @freq_subday_interval=1,     --重复周期数,这里每小时执行一次             @active_start_date = NULL,   --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD             @active_end_date = 99991231, --作业执行的停止日期,默认为99991231,格式为YYYYMMDD             @active_start_time = 00000, --作业执行的开始时间,格式为HHMMSS             @active_end_time = 235959    --作业执行的停止时间,格式为HHMMSS            --周调度     EXEC msdb.dbo.sp_add_jobschedule             @job_id = @jobid,             @name = N'调度名称',              @freq_type = 8,              --每周             @freq_recurrence_factor = 1, --每多少周执行一次,这里是每周             @freq_interval = 62,         --在星期几执行,由POWER(2,N)表示,N的值为0~6,代表星期日~星期六,如果指定两个,则将值相加,例如,值为65表示在星期天和星期日执行(POWER(2,0)+POWER(2,6))             @freq_subday_type = 0x8,     --重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次             @freq_subday_interval = 1,   --重复周期数,这里每小时执行一次             @active_start_date = NULL,   --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD             @active_end_date = 99991231, --作业执行的停止日期,默认为99991231,格式为YYYYMMDD             @active_start_time = 00000, --作业执行的开始时间,格式为HHMMSS             @active_end_time = 235959    --作业执行的停止时间,格式为HHMMSS            --月调度(每X个月的每月几号)     EXEC msdb.dbo.sp_add_jobschedule             @job_id = @jobid,             @name = N'调度名称',              @freq_type = 16,             --每月             @freq_recurrence_factor = 2, --每多少月执行一次,这里是每2个月             @freq_interval = 2,          --在执行月的第几天执行,这里是第2天             @freq_subday_type = 0x8,     --重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次             @freq_subday_interval = 1,   --重复周期数,这里每小时执行一次             @active_start_date = NULL,   --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD             @active_end_date = 99991231, --作业执行的停止日期,默认为99991231,格式为YYYYMMDD             @active_start_time = 00000, --作业执行的开始时间,格式为HHMMSS             @active_end_time = 235959    --作业执行的停止时间,格式为HHMMSS            --月调度(每X个月的相对时间)     EXEC msdb.dbo.sp_add_jobschedule             @job_id = @jobid,             @name = N'调度名称',              @freq_type = 32,             --每月             @freq_recurrence_factor = 2, --每多少月执行一次,这里是每2个月             @freq_interval = 9,          --在当月的那个时间执行,1~7=星期日至星期六,8=日 ,9=工作日,10=周末             @freq_relative_interval = 1, --在第几个相对时间执行,允许的值为1,2,4,8代表第1~4个相对时间,16表示最后一个相对时间             @freq_subday_type = 0x8,     --重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次             @freq_subday_interval = 1,   --重复周期数,这里每小时执行一次             @active_start_date = NULL,   --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD             @active_end_date = 99991231, --作业执行的停止日期,默认为99991231,格式为YYYYMMDD             @active_start_time = 00000, --作业执行的开始时间,格式为HHMMSS             @active_end_time = 235959    --作业执行的停止时间,格式为HHMMSS            --在特定时候执行的作业调度     EXEC msdb.dbo.sp_add_jobschedule             @job_id = @jobid,             @name = N'调度名称',              @freq_type = 64     --64=在SQLServerAgent 服务启动时运行,128=计算机空闲时运行            -----------------------------------------------------------------------------------------------    [sql] view plain copy在CODE上查看代码片派生到我的代码片     IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].SP_AUTO_CREATEJOB') AND OBJECTPROPERTY(ID, N'ISPROCEDURE') = 1)     DROP PROCEDURE [DBO].SP_AUTO_CREATEJOB     GO            CREATE PROCEDURE DBO.SP_AUTO_CREATEJOB                             @DBNAME VARCHAR(100),    --执行数据库                                @JOBNAME VARCHAR(100),                                  @EXECSQL NVARCHAR(4000) --执行语句           AS          PRINT '----作业 :'+@JOBNAME     PRINT '----开始执行:'  +@EXECSQL            BEGIN TRANSACTION        DECLARE            @JOBID UNIQUEIDENTIFIER,                @RETURNCODE INT             SELECT @RETURNCODE = 0                   BEGIN                  IF  EXISTS (SELECT JOB_ID FROM MSDB.DBO.SYSJOBS_VIEW WHERE NAME =@JOBNAME)          EXECUTE MSDB.DBO.SP_DELETE_JOB @JOB_NAME=@JOBNAME                           EXECUTE @RETURNCODE = MSDB.DBO.SP_ADD_JOB       --返回值:0成功,1失败                             @JOB_NAME = @JOBNAME,                                         @ENABLED = 1,               --状态。默认值为 1（启用）,为 0 不启用                             @OWNER_LOGIN_NAME = 'SA',   --拥有作业的登录名。默认值为 NULL,可解释为当前登录名                             @JOB_ID = @JOBID OUTPUT                             IF (@@ERROR <> 0 OR @RETURNCODE <> 0) GOTO QUITWITHROLLBACK                                     EXECUTE @RETURNCODE = MSDB.DBO.SP_ADD_JOBSTEP                              @JOB_ID = @JOBID,                                           @STEP_NAME = N'STEP1',      --步骤名称                                @SUBSYSTEM = 'TSQL',        --步骤的类型,一般为TSQL                                      @DATABASE_NAME = @DBNAME,   --执行数据库                                           @COMMAND = @EXECSQL         --执行语句                      IF (@@ERROR <> 0 OR @RETURNCODE <> 0) GOTO QUITWITHROLLBACK                                      EXECUTE @RETURNCODE = MSDB.DBO.SP_UPDATE_JOB                              @JOB_ID = @JOBID,                                            @START_STEP_ID = 1          --作业中要执行的第一个步骤的标识号         IF (@@ERROR <> 0 OR @RETURNCODE <> 0) GOTO QUITWITHROLLBACK                                            EXECUTE @RETURNCODE = MSDB.DBO.SP_ADD_JOBSERVER                              @JOB_ID = @JOBID,                                                @SERVER_NAME = N'(LOCAL)'                                  IF (@@ERROR <> 0 OR @RETURNCODE <> 0) GOTO QUITWITHROLLBACK                        END      COMMIT TRANSACTION                 GOTO ENDSAVE                                    QUITWITHROLLBACK:                  IF (@@TRANCOUNT > 0)            BEGIN                     ROLLBACK TRANSACTION                     RETURN 1           END                      ENDSAVE:            EXEC @RETURNCODE = MSDB.DBO.SP_START_JOB @JOB_ID = @JOBID  --启动作业      RETURN @RETURNCODE

创建作业的步骤：

创建作业的步骤一般如下所示：

执行 sp_add_job 来创建作业。
执行 sp_add_jobstep 来创建一个或多个作业步骤。
执行 sp_add_schedule 来创建计划。
执行 sp_attach_schedule 将计划附加到作业。
执行 sp_add_jobserver 来设置作业的服务器。

阅读全文

0 0