createsqljob.sql

来自「电子商务入门套件是一个电子商务网络店面」· SQL 代码 · 共 40 行

Declare @JobCount int,
         @DB_NAME varchar(250),
         @JobName varchar(250),
         @JobEnabled tinyint
         
set @DB_NAME = '~~##CMRCStarterKitDB##~~'
set @JobName = '~~##CMRCRemoveAbandonedCarts##~~'
SET @JobEnabled = ~~##CMRCRunJob##~~

USE msdb

SELECT @JobCount=Count(name) FROM sysjobs where name=@JobName

If @JobCount<>0   exec sp_delete_job @job_name=@JobName 

exec sp_add_job
   @job_name = @JobName,
   @enabled = @JobEnabled,
   @notify_level_eventlog = 3

exec sp_add_jobserver
   @job_name = @JobName,
   @server_name = "(local)"

exec sp_add_jobstep
   @job_name = @JobName,
   @step_name = 'Step 1',
   @command = "exec CMRC_ShoppingCartRemoveAbandoned",
   @database_name = @DB_NAME,
   @retry_attempts = 2,
   @retry_interval = 5

exec sp_add_jobschedule
   @job_name = @JobName,
   @name = 'Every day at 12AM',
   @freq_type = 4,
   @freq_interval = 1,
   @freq_subday_type = 1

GO