SQL语句创建SQL作业

1、根据sendTab的SendTime定制作，并且在该作业完成时,可以自动删除作业

2、--示例--测试表createtablesendTab(IDintidentity(1,1),Namevarchar(10) ,SendTimedatetime,AcceptUnitvarchar(10) ,SendUnitvarchar(10),Contentvarchar(8000))createtableaccepteTab(IDintidentity(1,1),Namevarchar(10) ,SendUnitvarchar(10),AcceptUnitvarchar(10),Contentvarchar(8000))go

3、--创建处理的存储过程createprocp_JobSet@idint,--要处理的sendTab的id@is_deletebit=0--是否仅删除,为0则否,为1则是asdeclare@dbnamesysname,@jobnamesysname ,@dateint,@timeintselect@jobname='定时发送作业_'+cast(@idasvarchar) ,@date=convert(varchar,SendTime,112) ,@time=replace(convert(varchar,SendTime,108),':','')fromsendTabwhereid=@idifexists(select1frommsdb..sysjobswherename=@jobname)execmsdb..sp_delete_job@job_name=@jobnameif@is_delete=1return

4、--创建作业execmsdb..sp_add_job@job_name=@jobname,@delete_level=1

5、--创建作业步骤declare@sqlvarchar(800)select@sql='挢旗扦渌;insert accept髫潋啜缅eTab(name,SendUnit,AcceptUnit,Content) select name,AcceptUnit,SendUnit,Content from sendTab where id='+cast(@idasvarchar) ,@dbname=db_name()execmsdb..sp_add_jobstep@job_name=@jobname,@step_name='发送处理步骤',@subsystem='TSQL',@database_name=@dbname,@command=@sql,@retry_attempts=5,--重试次数@retry_interval=5--重试间隔--创建调度EXECmsdb..sp_add_jobschedule@job_name=@jobname,@name='时间安排',@enabled=1,@freq_type=1,@active_start_date=@date,@active_start_time=@time--添加目标服务器EXECmsdb.dbo.sp_add_jobserver@job_name=@jobname,@server_name=N'(local)'go

6、--创建处理的触发器(新增/修改)createtriggertr_insert_updateonsendTabforinsert荑樊综鲶,updateasdeclare@idintdeclaretbcursorlocalforselectidfrominsertedopentbfetchnextfromtbinto@idwhile@@fetch_status=0beginexecp_JobSet@idfetchnextfromtbinto@idendclosetbdeallocatetbgo--创建处理的触发器(删除)createtriggertr_deleteonsendTabfordeleteasdeclare@idintdeclaretbcursorlocalforselectidfromdeletedopentbfetchnextfromtbinto@idwhile@@fetch_status=0beginexecp_JobSet@id,1fetchnextfromtbinto@idendclosetbdeallocatetbgo--测试--插入数据insertsendTabselect'文书1','2004/5/1 12:00:00','UnitA','UnitB','txt'unionallselect'文书2','2004/5/12 12:00:00','UnitA','UnitB','txt'unionallselect'文书3','2004/5/21 12:00:00','UnitA','UnitB','txt'--修改updatesendTabsetname='档案1',SendTime='2004/5/1 15:00:00'whereid=1--删除deletesendtabwhereid=3go--删除测试droptablesendTab,accepteTabdropprocp_JobSet