使用T-Sql脚本创建 Sql Server 作业

 

DECLARE @jobName NVARCHAR(128) = N'Update TelemetryReading & DailyChange & Order date';
DECLARE @scheduleName NVARCHAR(128) = N'Update TelemetryReading & DailyChange & Order date';

USE msdb;

-- 检查作业是否存在
IF EXISTS (
    SELECT *
    FROM dbo.sysjobs
    WHERE name = @jobName
)
BEGIN
    -- 删除现有的作业步骤
    EXEC dbo.sp_delete_jobstep
        @job_name = @jobName,
        @step_id = 1;

    -- 删除现有的作业
    EXEC dbo.sp_delete_job
        @job_name = @jobName;
END

-- 创建作业
EXEC dbo.sp_add_job
    @job_name = @jobName,
    @enabled = 1;

-- 添加作业步骤
EXEC dbo.sp_add_jobstep
    @job_name = @jobName,
    @step_name = N'YourStepName',
    @subsystem = N'TSQL',
    @command = N'
		USE OGS_Coregas_New;
		
		Update OGS_Coregas_New.dbo.TelemetryReading
		set ReadingTime = dateadd(day,1,ReadingTime);

		Update OGS_Coregas_New.dbo.DailyChange
		set [Date] = dateadd(day,1,[Date]);

		Update OGS_Coregas_New.dbo.[Order]
		set [OrderDate] = dateadd(day,1,[OrderDate]);
    ',
    @retry_attempts = 0,
    @retry_interval = 0;

-- 设置作业调度
EXEC dbo.sp_add_schedule
    @schedule_name = @scheduleName,
    @freq_type = 4, -- 每日
    @freq_interval = 1, -- 每天
    @active_start_time = 0, -- 凌晨 12:00:00
    @active_end_time = 235959; -- 晚上 11:59:59

-- 将作业和调度绑定
EXEC dbo.sp_attach_schedule
    @job_name = @jobName,
    @schedule_name = @scheduleName;