SQL Common job definition T-SQL template

--Define create job declare @jobid uniqueidentifierexec msdb.dbo.sp_add_job @job_name = N ' job name ', @job_id = @jobid OUTPUT--Define job step Declare @sql nvarchar, @dbname sysnameselect @dbname =db_name (),--the job step executes in the current database @sql =n '--Job step content '--generally defined as using the TSQL This defines the Transact-SQL statement to execute exec msdb.dbo.sp_add_jobstep @job_id = @jobid, @step_name = N ' job step name ', @subsystem = ' T SQL ',--the type of step, typically tsql @[email protected], @command = @sql-Create a schedule (using several specially defined job schedule templates) EXEC msdb.            Sp_add_jobschedule @job_id = @jobid, @name = N ' dispatch name ', @freq_type = 4,--Daily @freq_interval = 1,    --Specify how many days to occur, and this is 1 days.    @freq_subday_type =0x8,--repeat mode, 0x1= at the specified time, 0x4= how many minutes, 0x8= how many hours to execute once @freq_subday_interval = 1,--repeat the number of cycles, which is performed once per hour @active_start_date = NULL,--the start date of the job execution, NULL for the current date, the format yyyymmdd @active_end_date = 99991231,--the stop date of the job execution, the default is 99991231, The format is YYYYMMDD @active_start_time = 00000,--The start time of the job execution, in the format hhmmss @active_end_time = 235959--The stop time of the job execution, in the format HHMMSS-- Add Target server Declare @servername sysnameset @servername =convert (nvarchar (+), serverproperty (N ' servername ')) EXEC msdb.dbo.sp_add_ JobServer @job_id = @jobid, @server_name = @servername--Use the current SQL instance--schedule template to define--/*--Day schedule exec msdb.            Sp_add_jobschedule @job_id = @jobid, @name = N ' dispatch name ', @freq_type = 4,--Daily @freq_interval = 1,    --Specify how many days to occur, and this is 1 days.    @freq_subday_type =0x8,--repeat mode, 0x1= at the specified time, 0x4= how many minutes, 0x8= how many hours to execute once @freq_subday_interval = 1,--repeat the number of cycles, which is performed once per hour @active_start_date = NULL,--the start date of the job execution, NULL for the current date, the format yyyymmdd @active_end_date = 99991231,--the stop date of the job execution, the default is 99991231, The format is YYYYMMDD @active_start_time = 00000,--The start time of the job execution, in the format hhmmss @active_end_time = 235959--The stop time of the job execution, in the format hhmmss--*     /--/*--Week dispatch exec msdb.dbo.sp_add_jobschedule @job_id = @jobid, @name = N ' dispatch name ', @freq_type = 8,--weekly @freq_recurrence_factor = 1,--once every week, here is the weekly @freq_interval = 62,--on the day of the week, represented by Power (2,n), the value of N is 0~6, representing Sunday ~ Saturday , if you specify two, the value is added, for example, a value of 65 indicatesExecuted in Sunday and Sunday (POWER (2,0) +power (2,6)) @freq_subday_type = 0x8,--repeat mode, 0x1= at a specified time, 0x4= how many minutes, 0x8= how many hours to execute once @freq_subday_  Interval = 1,--the number of recurrence, which is performed once per hour @active_start_date = NULL,--the start date of the job execution, the current date as NULL, and the format of YYYYMMDD @active_end_date  = 99991231,--the stop date of the job execution, the default is 99991231, the format is YYYYMMDD @active_start_time = 00000,--The start time of the job execution, in the format HHMMSS @active_end_time = 235959-The stop time of the job execution, formatted as hhmmss--*/--/*--month (every x months of the month) EXEC msdb.dbo.sp_add_jobschedule @job_id = @jobid, @name           = N ' dispatch name ', @freq_type = 16,--Monthly @freq_recurrence_factor = 2,--every number of months, here is every 2 months @freq_interval = 2, --in the day of execution of the month, here is the 2nd day @freq_subday_type = 0x8,--Repeat the way, 0x1= at the specified time, 0x4= how many minutes, 0x8= how many hours to execute once @freq_subday_inte Rval = 1,--the number of recurrence, which is performed once per hour @active_start_date = NULL,--the start date of the job execution, the current date as NULL, and the format YYYYMMDD @active_end_date = 9 9991231,--the stop date of the job execution, the default is 99991231, the format is YYYYMMDD @active_start_time = 00000,--The start time of the job execution, in the format hhmmss @active_end_time = 2 35959--The stop time of the job execution, in the formathhmmss--*/--/*--Month Dispatch (relative time per x months) EXEC msdb.dbo.sp_add_jobschedule @job_id = @jobid, @name = N ' dispatch name ', @freq_type = 32,--monthly @freq_recurrence_factor = 2,--every number of months, here is every 2 months @freq_interval = 9,--at that time of the month, 1~7= Star Date to Saturday, 8= day, 9 = weekday, 10 = Weekend @freq_relative_interval = 1,--at the first few relative times, the allowable value is 1,2,4,8 for the 1th to 4th relative time, and 16 represents 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 once @freq_subday_interval = 1,--repeat the number of cycles, which is performed once per hour @active_start    _date = NULL,--the start date of the job execution, NULL for the current date, the format of YYYYMMDD @active_end_date = 99991231,--the stop date of the job execution, the default is 99991231, and the format is YYYYMMDD @active_start_time = 00000,--The start time of the job execution, in the format hhmmss @active_end_time = 235959--The stop time of the job execution, in the format hhmmss--*/--/*--at a particular time Job Schedule exec Msdb.dbo.sp_add_jobschedule @job_id = @jobid, @name = N ' dispatch name ', @freq_type = 64--64= in Sqlserverag ENT service runs at startup, 128= computer is idle when run--*/--/*--only executes once job scheduling exec msdb.              Sp_add_jobschedule @job_id = @jobid, @name = N ' dispatch name ', @freq_type = 1,  --only once @active_start_date = null,--start date for job execution, NULL for current date, format yyyymmdd @active_start_time = 00000--Start time of job execution , formatted as hhmmss--*/

SQL Common job definition T-SQL template