SQL statement to create job scheduling under SQL Server 2000

SQL statement to create job scheduling under SQL Server 2000

-- Define job creation
DECLARE @ jobid uniqueidentifier
EXEC msdb. dbo. sp_add_job
@ Job_name = n' job name ',
@ Job_id = @ jobid OUTPUT

-- Define job steps
DECLARE @ SQL nvarchar (400), @ dbname sysname
SELECT @ dbname = DB_NAME (), -- the job step is executed in the current database
@ SQL = n' -- job step content' -- generally defines jobs that are processed using TSQL. Here, the Transact-SQL statement to be executed is defined.
EXEC msdb. dbo. sp_add_jobstep
@ Job_id = @ jobid,
@ Step_name = n' job step name ',
@ Subsystem = 'tsql', -- step type, generally TSQL
@ Database_name = @ dbname,
@ Command = @ SQL

-- Create Scheduling (use the following job scheduling templates)
EXEC msdb .. sp_add_jobschedule
@ Job_id = @ jobid,
@ Name = n' scheduling name ',
@ Freq_type = 4, -- daily
@ Freq_interval = 1, -- specifies the number of days that occur. Here is 1 day.
@ Freq_subday_type = 0x8, -- repeat mode, 0x1 = at the specified time, 0x4 = How many minutes, 0x8 = How many hours to execute
@ Freq_subday_interval = 1, -- number of repetition cycles, which is executed once every hour
@ Active_start_date = NULL, -- start date of job execution. If it is NULL, it indicates the current date. The format is YYYYMMDD.
@ Active_end_date = 99991231, -- end date of job execution. The default value is 99991231, in the format of YYYYMMDD.
@ Active_start_time = 00000, -- start time of job execution, in the format of HHMMSS
@ Active_end_time = 235959 -- stop time of job execution, in the format of HHMMSS

-- Add the target server
DECLARE @ servername sysname
SET @ servername = CONVERT (nvarchar (128), SERVERPROPERTY (N 'servername '))
EXEC msdb. dbo. sp_add_jobserver
@ Job_id = @ jobid,
@ Server_name = @ servername -- use the current SQL instance


-- Scheduling template Definition
--/* -- Daily Scheduling
EXEC msdb .. sp_add_jobschedule
@ Job_id = @ jobid,
@ Name = n' scheduling name ',
@ Freq_type = 4, -- daily
@ Freq_interval = 1, -- specifies the number of days that occur. Here is 1 day.
@ Freq_subday_type = 0x8, -- repeat mode, 0x1 = at the specified time, 0x4 = How many minutes, 0x8 = How many hours to execute
@ Freq_subday_interval = 1, -- number of repetition cycles, which is executed once every hour
@ Active_start_date = NULL, -- start date of job execution. If it is NULL, it indicates the current date. The format is YYYYMMDD.
@ Active_end_date = 99991231, -- end date of job execution. The default value is 99991231, in the format of YYYYMMDD.
@ Active_start_time = 00000, -- start time of job execution, in the format of HHMMSS
@ Active_end_time = 235959 -- stop time of job execution, in the format of HHMMSS
--*/

--/* -- Weekly scheduling
EXEC msdb. dbo. sp_add_jobschedule
@ Job_id = @ jobid,
@ Name = n' scheduling name ',
@ Freq_type = 8, -- weekly
@ Freq_recurrence_factor = 1, -- the number of weeks of execution.
@ Freq_interval = 62, -- run on the day of the week, represented by POWER (2, N). The value of N is 0 ~ 6, representing Sunday ~ If two values are specified for Saturday, the values are added. For example, if the value is 65, the values are executed on Sunday and Sunday (POWER () + POWER ))
@ Freq_subday_type = 0x8, -- repeat mode, 0x1 = at the specified time, 0x4 = How many minutes, 0x8 = How many hours to execute
@ Freq_subday_interval = 1, -- number of repetition cycles, which is executed once every hour
@ Active_start_date = NULL, -- start date of job execution. If it is NULL, it indicates the current date. The format is YYYYMMDD.
@ Active_end_date = 99991231, -- end date of job execution. The default value is 99991231, in the format of YYYYMMDD.
@ Active_start_time = 00000, -- start time of job execution, in the format of HHMMSS
@ Active_end_time = 235959 -- stop time of job execution, in the format of HHMMSS
--*/

--/* -- Monthly scheduling (number of months per X months)
EXEC msdb. dbo. sp_add_jobschedule
@ Job_id = @ jobid,
@ Name = n' scheduling name ',
@ Freq_type = 16, -- monthly
@ Freq_recurrence_factor = 2, -- the number of months for execution. Here it is every two months.
@ Freq_interval = 2, -- execute the Statement on the day of the execution month. The value is 2nd days.
@ Freq_subday_type = 0x8, -- repeat mode, 0x1 = at the specified time, 0x4 = How many minutes, 0x8 = How many hours to execute
@ Freq_subday_interval = 1, -- number of repetition cycles, which is executed once every hour
@ Active_start_date = NULL, -- start date of job execution. If it is NULL, it indicates the current date. The format is YYYYMMDD.
@ Active_end_date = 99991231, -- end date of job execution. The default value is 99991231, in the format of YYYYMMDD.
@ Active_start_time = 00000, -- start time of job execution, in the format of HHMMSS
@ Active_end_time = 235959 -- stop time of job execution, in the format of HHMMSS
--*/

--/* -- Monthly scheduling (relative time of every X months)
EXEC msdb. dbo. sp_add_jobschedule
@ Job_id = @ jobid,
@ Name = n' scheduling name ',
@ Freq_type = 32, -- monthly
@ Freq_recurrence_factor = 2, -- the number of months for execution. Here it is every two months.
@ Freq_interval = 9, -- execute at that time of the current month, 1 ~ 7 = Sunday to Saturday, 8 = Day, 9 = workday, 10 = weekend
@ Freq_relative_interval = 1, -- execute at the relative time. The allowed values are 1st, and 8 represent ~ Four relative times. 16 indicates the last relative time.
@ Freq_subday_type = 0x8, -- repeat mode, 0x1 = at the specified time, 0x4 = How many minutes, 0x8 = How many hours to execute
@ Freq_subday_interval = 1, -- number of repetition cycles, which is executed once every hour
@ Active_start_date = NULL, -- start date of job execution. If it is NULL, it indicates the current date. The format is YYYYMMDD.
@ Active_end_date = 99991231, -- end date of job execution. The default value is 99991231, in the format of YYYYMMDD.
@ Active_start_time = 00000, -- start time of job execution, in the format of HHMMSS
@ Active_end_time = 235959 -- stop time of job execution, in the format of HHMMSS
--*/

--/* -- Job scheduling executed at specific times
EXEC msdb. dbo. sp_add_jobschedule
@ Job_id = @ jobid,
@ Name = n' scheduling name ',
@ Freq_type = 64 -- 64 = run when the SQLServerAgent service is started, 128 = run when the computer is idle
--*/

--/* -- Only executes the Job Scheduling once.
EXEC msdb .. sp_add_jobschedule
@ Job_id = @ jobid,
@ Name = n' scheduling name ',
@ Freq_type = 1, -- run only once
@ Active_start_date = NULL, -- start date of job execution. If it is NULL, it indicates the current date. The format is YYYYMMDD.
@ Active_start_time = 00000 -- start time of job execution, in the format of HHMMSS
--*/