SQL[作业]定时执行.docx

SQL[作业]定时执行 SQL Server 2005 要安装SP2补丁包 启动 SQL Server Analysis Services 服务 创建测试表 CREATE TABLE [Test] ( [ID] bigint NULL, [cCode] varchar(50) COLLATE Chinese_PRC_CI_AS NULL, [cName] varchar(50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO EXEC sp_addextendedproperty MS_Description, NID, schema, dbo, table, Test, column, ID GO EXEC sp_addextendedproperty MS_Description, N学号, schema, dbo, table, Test, column, cCode GO EXEC sp_addextendedproperty MS_Description, N姓名, schema, dbo, table, Test, column, cName GO 创建作业的脚本语句 ************************************************************************************* USE [msdb] GO /****** 对象: Job [First_Job] 脚本日期: 12/11/2014 14:57:52 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** 对象: JobCategory [[Uncategorized (Local)]]] 脚本日期: 12/11/2014 14:57:52 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N[Uncategorized (Local)] AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=NJOB, @type=NLOCAL, @name=N[Uncategorized (Local)] IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=NFirst_Job, /*作业名称*/ @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)], @owner_login_name=NSealZhang-PC\SealZhang, /* @owner_login_name=Nsa */ @job_id = @jobId OUTPUT IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback /****** 对象: Step [First_Step] 脚本日期: 12/11/2014 14:57:53 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=NFirst_Step, /*作业步骤名称*/ @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_in