sql server作业用法.docx

1、sql server作业用法sql server作业用法 一作业的运用（自动更新db）1、打开sql server的企业管理器，找到管理中的作业2、新增一个作业，将具体的设置进行设定，新增步骤以确定要处理的sp及sql语句新增调度以确定要执行的频率!3、例子 二处理SP及其它sql语句以进行作业处理（运用数据仓库的模式）1、创建要进行保存数据的空间(一般为表)2、运用作业进行数据填充3、通过存的数据进行数据呈现三具体示例1、创建表if exists (select * from dbo.sysobjects where id = object_id(Ndbo.r_Count1) and OBJ

2、ECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.r_Count1GOCREATE TABLE dbo.r_Count1 ( UnitCoding varchar (15) COLLATE Chinese_PRC_CI_AS NOT NULL , StatDate varchar (10) COLLATE Chinese_PRC_CI_AS NOT NULL , Field1 int NOT NULL , Field2 int NOT NULL , Field3 int NOT NULL , Field4 int NOT NULL , Field

3、5 int NOT NULL , Field6 int NOT NULL , Field7 int NOT NULL , Field8 int NOT NULL , Field9 int NOT NULL , Field10 int NOT NULL , Field11 int NOT NULL , Field12 int NOT NULL , Field13 int NOT NULL , Field14 int NOT NULL , Field15 int NOT NULL , Field16 int NOT NULL , Field17 int NOT NULL , Field18 int

4、 NOT NULL ) ON PRIMARYGO2、处理数据(运用作业的sp)ALTER PROCEDURE AutoExec_Count1(UnitCoding varchar(20),StatDate datetime)AS /* 向临时表中插入要变更单位的新旧单位代码 */Declare i_SFirstdate varchar(10)Declare i_EFirstdate varchar(10)Declare i_SSeconddate varchar(10)Declare i_ESeconddate varchar(10)Declare i_getdate datetimeSele

5、ct i_getdate = StatDateSelect i_SFirstdate=dbo.u_Date2Char(DATEADD(mm,DATEDIFF(mm,0,i_getdate),0)-当月第1天Select i_EFirstdate=dbo.u_Date2Char(DATEADD(dd,14,DATEADD(mm,DATEDIFF(mm,0,i_getdate),0) -当月第15天Select i_SSeconddate=dbo.u_Date2Char(DATEADD(dd,15,DATEADD(mm,DATEDIFF(mm,0,i_getdate),0) -当月第16天Sele

6、ct i_ESeconddate=dbo.u_Date2Char(DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,i_getdate)+1,0) -当月最后1天/* 启动事务 */Begin transaction/* 创建用于保存单位代码和报表周期的临时表 */Create Table #TblUnitDateSwap(NumberID int IDENTITY (1, 1) NOT NULL ,UnitCoding varchar(20),startdate varchar(10),enddate varchar(10)/* 向临时表中插入单位数据 */Sele

7、ct UnitCoding = RTrim(UnitCoding) + %-插入上半月统计日期(例:2004-1-12004-1-15)Insert Into #TblUnitDateSwap(UnitCoding,startdate,enddate)Select UnitCoding,i_SFirstdate,i_EFirstdate From m_Units Where Len(UnitCoding)=12 And (UnitCoding Like UnitCoding)-插入下半月统计日期(例:2004-1-162004-1-31)Insert Into #TblUnitDateSwap

8、(UnitCoding,startdate,enddate)Select UnitCoding,i_SSeconddate,i_ESeconddate From m_Units Where Len(UnitCoding)=12 And (UnitCoding Like UnitCoding)-插入整月统计日期(例:2004-1-12004-1-31)Insert Into #TblUnitDateSwap(UnitCoding,startdate,enddate) Select UnitCoding,i_SSeconddate,i_ESeconddate From m_Units Where

9、Len(UnitCoding)=12 And (UnitCoding Like UnitCoding)/*插入存储过程开始执行时间(正式执行时屏蔽此功能)*/INSERT INTO r_ExecTime(UnitCoding,ExecName) VALUES(UnitCoding,START)Declare errorcode intDeclare i_UnitCoding varchar(20)Declare i_startdate varchar(10)Declare i_enddate varchar(10)/* 统计插入临时表中的记录数 */Declare v_TempTableCou

10、nt intSelect v_TempTableCount = count(*) From #TblUnitDateSwapDeclare i integer - 定义一个临时循环变量Select i=1 - 初始化临时变量为1-循环每个基层单位(单位代码长度为12)，将统计值保存到r_Count对应表中While(i = v_TempTableCount) - 执行循环的条件为临时变量=startdate And StatDate =enddate And UnitCoding Like Unitcoding + % Group By Left(UnitCoding,v_Len) Order By Left(UnitCoding,v_Len) RETURNEND