请教：如何在一个存储过程里用语句动态的创建一个job，执行另外一个存储过程？

Question

你的数据库服务器-》企业管理器－》管理－》SQL Server 代理－》作业－》新建作业，按照要求填就可以了，在“步骤”的“命令”中填写你要执行的存储过程或语句，在“调度”里面填写什么时间执行
EXEC sp_add_job @job_name = '作业名字'
EXEC sp_add_jobstep @job_name = '作业名字',
   @step_name = '步骤名子',
   @subsystem = 'TSQL',
   @command = 'EXEC 库名..过程名', 
   @retry_attempts = 5, --重试次数
   @retry_interval = 5  --重试间隔EXEC sp_add_jobschedule @job_name = '作业名字', 
   @name = '作业调度名字',
   @freq_type = 4, -- 每天
   @freq_interval = 26, --间隔
   @active_start_time = 10000 --开始时间

Answer 1

EXEC sp_add_job @job_name = '作业名字'
EXEC sp_add_jobstep @job_name = '作业名字',
   @step_name = '步骤名子',
   @subsystem = 'TSQL',
   @command = 'EXEC 库名..过程名', 
   @retry_attempts = 5, --重试次数
   @retry_interval = 5  --重试间隔EXEC sp_add_jobschedule @job_name = '作业名字', 
   @name = '作业调度名字',
   @freq_type = 4, -- 每天
   @freq_interval = 26, --间隔
   @active_start_time = 10000 --开始时间

Answer 2

谢谢楼上两位的帮助，另外还有个问题：
我动态创建的job只执行一次，执行完后，需要用语句删除吗？
如果要删除，我又怎么判断该job执行结束呢？

Answer 3

--楼上的忽略了一个问题,必须指明msdb数据库,否则会出现错误--创建作业
exec msdb..sp_add_job @job_name='数据处理'--创建作业步骤
declare @sql varchar(800),@dbname varchar(250)
select @sql='exec p_createxml'  --数据处理的命令
,@dbname=db_name() --执行数据处理的数据库名exec msdb..sp_add_jobstep @job_name='数据处理',
@step_name = '数据同步',
@subsystem = 'TSQL',
@database_name=@dbname,
    @command = @sql
@retry_attempts = 5,  --重试次数
@retry_interval = 5   --重试间隔--创建调度
EXEC msdb..sp_add_jobschedule @job_name = '数据处理', 
   @name = '时间安排',
   @freq_type = 4,  --每天
   @freq_interval = 1, --每天执行一次
   @active_start_time = 00000 --0点执行

Answer 4

多谢以上各位，还有个问题不明白：
如果我的job只执行一次，并且是要立即执行，在schedule里面怎么指定参数？

Answer 5

--如果要求只执行一次,可以指定结束日期和结束时间来控制.--完整一点的.--如果作业已经存在,则删除它,这样就解决了作业执行后的删除问题
if exists(SELECT 1 FROM msdb.dbo.sysjobs where name='数据处理')
EXEC msdb.dbo.sp_delete_job @job_name = N'数据处理' 
--创建作业
exec msdb..sp_add_job @job_name='数据处理'--创建作业步骤
declare @sql varchar(800),@dbname varchar(250)
select @sql='exec p_createxml'  --数据处理的命令
,@dbname=db_name() --执行数据处理的数据库名exec msdb..sp_add_jobstep @job_name='数据处理',
@step_name = '数据同步',
@subsystem = 'TSQL',
@database_name=@dbname,
    @command = @sql,
@retry_attempts = 5,  --重试次数
@retry_interval = 5   --重试间隔
--创建调度
declare @d int
select @d=cast(convert(varchar,getdate(),112) as int)  --仅今天执行,也可以手工指定执行日期,这样解决只执行一次的问题.
EXEC msdb..sp_add_jobschedule @job_name = '数据处理', 
   @name = '时间安排',
   @freq_type = 4,  --每天
   @freq_interval = 1, --每天执行一次
   @active_start_time = 00000, --0点执行
   @active_end_date = @d,          --结束日期,通过指定它,来控制
   @active_end_time = 235959 --结束时间

Answer 6

zjcxc(邹建):谢谢关注，如果我直接调用存储过程直接执行，则我的调用存储过程就阻塞在调用那里；
我的想法是调用存储过程将被调用的存储过程通过job方式，提交到后台执行，然后调用存储过程又去接受其它的需要被调用的存储过程，继续提交到后台通过job方式执行

Answer 7

另外我再顺便请教一个很菜的问题：如何在add job时候取得job_id？

Answer 8

--用下面的方法就可以取得job_id:declare @job_id int
exec msdb..sp_add_job @job_name='数据处理',@job_id= @job_id OUTPUTselect 创建的作业id=@job_id

Answer 9

--上面的类型定义错了,应该是:declare @job_id uniqueidentifier
exec msdb..sp_add_job @job_name='数据处理',@job_id= @job_id OUTPUTselect 创建的作业id=@job_idexec msdb..sp_delete_job @job_name='数据处理'