sql創建作業--自動執行存儲過程

創建自動執行存儲過程：

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