SQL statement to create job scheduling under SQL Server 2000

Source: Internet
Author: User

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
--*/

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.