SQL Server -- 作业(Job) 浅析1

华风

　　作业介绍
　　SQL SERVER的作业是一系列由SQLSERVER代理按顺序执行的指定操作。作业可以执行一系列活动，包括运行Transact-SQL脚本、命令行应用程序、Microsoft ActiveX脚本、IntegrationServices 包、Analysis Services 命令和查询或复制任务。作业可以运行重复任务或那些可计划的任务，它们可以通过生成警报来自动通知用户作业状态，从而极大地简化了SQL Server 管理[参见MSDN]。
　　创建作业、删除作业、查看作业历史记录....等所有操作都可以通过SSMS管理工具GUI界面操作，有时候也确实挺方便的。但是当一个实例有多个作业或多个数据库实例时，通过图形化的界面去管理、维护作业也是个头痛的问题，对于SQL脚本与GUI界面管理维护作业熟优熟劣这个问题，只能说要看场合。下面主要介绍通过SQL脚本来管理、维护作业。
　　作业分类
　　创建作业时，往往需要指定作业类别，如果不指定新建作业类别，就会默认为“[未分类(本地)]”，如下截图所示：

　　当然，你可以查看、添加、删除、修改作业分类。请看下面操作。
　　1：查看作业分类
　　Code Snippet
　　--method 1:
　　EXECmsdb.dbo.sp_help_category;
　　GO
　　--method 2:
　　SELECT  category_id            ,--作业类别ID
　　category_class         ,--类别中项目类型：1=作业2=警报 3=操作员
　　category_type          ,--类别中类型：=本地、=多服务器、=无
　　name                    --分类名称
　　FROMmsdb.dbo.syscategories
　　有兴趣的可以研究一下存储过程msdb.dbo.sp_help_category
　　SETQUOTED_IDENTIFIER OFF
　　SET ANSI_NULLS ON
　　GO
　　CREATEPROCEDUREsp_help_category
　　@class  VARCHAR(8)  ='JOB',
　　@type   VARCHAR(12)  =NULL,
　　@name  sysname      =NULL,
　　@suffixBIT         =0
　　AS
　　BEGIN
　　DECLARE@retval        INT
　　DECLARE@type_in       VARCHAR(12)
　　DECLARE@category_type  INT
　　DECLARE@category_classINT
　　DECLARE@where_clause   NVARCHAR(255)
　　DECLARE@cmd           NVARCHAR(255)
　　SET NOCOUNT ON
　　SELECT@class=LTRIM(RTRIM(@class))
　　SELECT@type  =LTRIM(RTRIM(@type))
　　SELECT@name  =LTRIM(RTRIM(@name))
　　IF (@type='') SELECT@type=NULL
　　IF (@name= N'') SELECT@name=NULL
　　IF (@class='JOB') AND (@typeISNULL)
　　SELECT@type_in='LOCAL'
　　ELSE
　　IF (@class'JOB') AND (@typeISNULL)
　　SELECT@type_in='NONE'
　　ELSE
　　SELECT@type_in=@type
　　EXECUTE@retval= sp_verify_category@class,
　　@type_in,
　　NULL,
　　@category_class OUTPUT,
　　@category_type  OUTPUT
　　IF (@retval0)
　　RETURN(1)
　　IF (@suffix0)
　　SELECT@suffix=1
　　IF@nameISNOTNULLAND
　　NOTEXISTS(SELECT*FROMmsdb.dbo.syscategories WHERE name =@name
　　AND category_class=@category_class)
　　BEGIN
　　DECLARE@category_class_stringNVARCHAR(25)
　　SET@category_class_string=CAST(@category_classASnvarchar(25))
　　RAISERROR(14526, -1, -1, @name, @category_class_string)
　　RETURN(1)
　　END
　　SELECT@where_clause= N'WHERE (category_class= '+CONVERT(NVARCHAR, @category_class) + N') '
　　IF (@nameISNOTNULL)
　　SELECT@where_clause=@where_clause+ N'AND (name = N'+QUOTENAME(@name, '''') + N') '
　　IF (@typeISNOTNULL)
　　SELECT@where_clause=@where_clause+ N'AND (category_type= '+CONVERT(NVARCHAR, @category_type) + N') '
　　SELECT@cmd= N'SELECTcategory_id, '
　　IF (@suffix=1)
　　BEGIN
　　SELECT@cmd=@cmd+ N'''category_type''= '
　　SELECT@cmd=@cmd+ N'CASEcategory_type '
　　SELECT@cmd=@cmd+ N'WHEN 0 THEN''NONE'''
　　SELECT@cmd=@cmd+ N'WHEN 1 THEN''LOCAL'''
　　SELECT@cmd=@cmd+ N'WHEN 2 THEN''MULTI-SERVER'''
　　SELECT@cmd=@cmd+ N'WHEN 3 THEN''NONE'''
　　SELECT@cmd=@cmd+ N'ELSEFORMATMESSAGE(14205) '
　　SELECT@cmd=@cmd+ N'END, '
　　END
　　ELSE
　　BEGIN
　　SELECT@cmd=@cmd+ N'category_type,'
　　END
　　SELECT@cmd=@cmd+ N'name '
　　SELECT@cmd=@cmd+ N'FROMmsdb.dbo.syscategories '
　　EXECUTE (@cmd+@where_clause+ N'ORDER BYcategory_type, name')
　　RETURN(@@error)
　　END
　　GO
　　sp_help_category
　　2：添加作业分类
　　如下所示，添加一个叫"DBA_MONITORING"的作业分类
　　Code Snippet
　　EXECmsdb.dbo.sp_add_category
　　@class=N'JOB',
　　@type=N'LOCAL',
　　@name=N'DBA_MONITORING' ;
　　GO
　　SELECT * FROMmsdb.dbo.syscategories WHERE NAME='DBA_MONITORING'
　　category_id category_class category_type    name
　　------------------------- ------------- -------------
　　102         1              1             DBA_MONITORING
　　有兴趣的可以研究一下存储过程msdb.dbo.sp_add_category
　　SET QUOTED_IDENTIFIER OFF
　　SET ANSI_NULLS ON
　　GO
　　CREATEPROCEDUREsp_add_category
　　@classVARCHAR(8)  ='JOB',
　　@type  VARCHAR(12)  ='LOCAL',
　　@name  sysname
　　AS
　　BEGIN
　　DECLARE@retval         INT
　　DECLARE@category_type  INT
　　DECLARE@category_classINT
　　SET NOCOUNT ON
　　SELECT@class=LTRIM(RTRIM(@class))
　　SELECT@type  =LTRIM(RTRIM(@type))
　　SELECT@name  =LTRIM(RTRIM(@name))
　　EXECUTE@retval= sp_verify_category @class,
　　@type,
　　@name,
　　@category_class OUTPUT,
　　@category_type  OUTPUT
　　IF (@retval0)
　　RETURN(1)
　　IF (EXISTS (SELECT*
　　FROM msdb.dbo.syscategories
　　WHERE (category_class =@category_class)
　　AND (name =@name)))
　　BEGIN
　　RAISERROR(14261, -1, -1, '@name', @name)
　　RETURN(1)
　　END
　　INSERTINTOmsdb.dbo.syscategories (category_class, category_type, name)
　　VALUES (@category_class, @category_type, @name)
　　RETURN(@@error)
　　END
　　GO
　　3：删除作业分类
　　如下所示，删除一个叫"DBA_MONITORING" 的作业分类
　　Code Snippet
　　EXECmsdb.dbo.sp_delete_category
　　@name=N'DBA_MONITORING',
　　@class=N'JOB';
　　GO
　　有兴趣的可以研究一下存储过程msdb.dbo.sp_delete_category