IDC资讯中心
if exists (select * from dbo.sysobjects where id = object_id(n[dbo].[p_jobset]) and objectproperty(id, nisprocedure) = 1)drop procedure [dbo].[p_jobset]go
/*–定时调用存储过程
创建一个在指定时间,调用指定存储过程的作业 作业执行完成后会自动删除
–邹建 2004.07–*/
/*–调用示例
exec p_jobset master.dbo.xp_cmdshell,2004-1-1 10:30–*/create proc p_jobset@prorcname sysname, –要调用定时调用的存储过程名,如果不在当前库中,则用:库名.所有者名.存储过程名@job_date datetime –存储过程的执行时间(包括时间信息)asdeclare @dbname sysname,@jobname sysname ,@date int,@time int
select @jobname=定时发送作业_+cast(newid() as varchar(36)) ,@date=convert(varchar,@job_date,112) ,@time=replace(convert(varchar,@job_date,108),:,)
if exists(select 1 from msdb..sysjobs where name=@jobname) exec msdb..sp_delete_job @job_name=@jobname
–创建作业exec msdb..sp_add_job @job_name=@jobname,@delete_level=1
–创建作业步骤declare @sql varchar(800)select @sql=exec +@prorcname ,@dbname=db_name()
exec msdb..sp_add_jobstep @job_name=@jobname, @step_name = 发送处理步骤, @subsystem = tsql, @database_name=@dbname, @command = @sql, @retry_attempts = 5, –重试次数 @retry_interval = 5 –重试间隔
–创建调度exec msdb..sp_add_jobschedule @job_name = @jobname, @name = 时间安排, @enabled = 1, @freq_type = 1, @active_start_date = @date, @active_start_time = @time
— 添加目标服务器exec msdb.dbo.sp_add_jobserver @job_name = @jobname , @server_name = n(local) go
