创建自动执行存储过程：
1.创建参数
2.删除已有同名的作业
3. 创建作业
4.创建作业步骤
5.连接服务器
6.创建作业调度
7.启动作业
ALTER PROCEDURE dbo.sx_pro_AutoExecJobCreation
AS
Begin
Declare @jobName varchar(20)
Declare @stepName varchar(20)
Declare @scheNmae varchar(20)
declare @sql varchar(1000)
declare @db_name varchar(30)
--dbo.webpage_getData_KPM,webpageKPMalert
--dbo.webpage_getData_scrap,webPageScrapRate
--dbo.webpage_ScrapKPM_Calculation,webpageScrapKPMcal
--dbo.webpage_calScrap,webpage_calScrap
--dbo.webpage_calKpm,webpage_calKpm
set @sql='exec dbo.webpage_calScrap' --执行存储过程
set @db_name=db_name()
set @jobName='webpage_calScrap'
set @stepName='webpage_calScrap'
set @scheNmae='webpage_calScrap'
--删除作业
if exists(SELECT * FROM msdb.dbo.sysjobs WHERE Name=@jobName)
begin
exec msdb.dbo.sp_delete_job
@job_name =@jobName
end
--创建作业
EXEC msdb.dbo.sp_add_job
@job_name =@jobName
--创建作业步骤
Exec msdb.dbo.sp_add_jobstep
@job_name=@jobName,
@step_name=@stepName,
@subsystem='TSQL',
@database_name=@db_name,
@command=@sql,
@retry_attempts=4,
@retry_interval=5
--连接服务器
EXEC msdb.dbo.sp_add_jobserver
@job_name=@jobName,
@server_name=N'(local)'
--创建作业调度
EXEC msdb.dbo.sp_add_jobschedule
@job_name = @jobName, -- Job name
@name = @scheNmae, -- Schedule name
@freq_type =4,
@freq_interval =1,
@freq_subday_type=0x1,
@freq_subday_interval=1,
@active_start_date =null,
@active_end_date =20550101,
@active_start_time = 062000,
@active_end_time = null;
EXEC msdb.dbo.sp_start_job @jobName
end