----- Create a script for a scheduled job ------

Create a JOB using a script:

If exists (select * from dbo. sysobjects where id = object_id (N [dbo]. [p_createjob]) and OBJECTPROPERTY (id, NIsProcedure) = 1)

Drop procedure [dbo]. [p_createjob] GO

Create proc p_createjob

@ Jobname varchar (100), -- job name

@ SQL varchar (8000), -- command to be executed

@ ServerName sysname =, -- job server name

@ Dbname sysname =, -- the default value is the current database name.

@ Freqtype varchar (6) = day, -- time period, month, week, day

@ Fsinterval int = 1, -- the number of repetitions relative to the day

@ Time int = 170000 -- execution start time. For repeated jobs, the time ranges from 0.

As

If isnull (@ dbname, '') =''

Set @ dbname = db_name () -- create a job

Exec MSDB .. sp_add_job @ job_name = @ jobname -- procedure for creating a job

Exec MSDB .. sp_add_jobstep @ job_name = @ jobname,

@ Step_name = data processing,

@ Subsystem = tsql,

@ Database_name = @ dbname,

@ Command = @ SQL,

@ Retry_attempts = 5, -- number of retries

@ Retry_interval = 5 -- Retry Interval -- create Scheduling

 

Declare @ ftype int, @ fstype int, @ ffactor int

Select @ ftype = case @ freqtype when day then 4 when week then 8 when month then 16 end,

@ Fstype = case @ fsinterval when 1 then 0 else 8 end

If @ fsinterval <> 1

Set @ time = 0 set @ ffactor = case @ freqtype when day then 0 else 1 end

EXEC msdb .. sp_add_jobschedule

@ Job_name = @ jobname, @ name = schedule,

@ Freq_type = @ ftype, -- every day, 8 weeks, 16 months

@ Freq_interval = 1, -- number of repeated executions

@ Freq_subday_type = @ fstype, -- whether to execute repeatedly

@ Freq_subday_interval = @ fsinterval, -- recurrence

@ Freq_recurrence_factor = @ ffactor,

@ Active_start_time = @ time -- execute at 17:00:00 pm

If @ servername = '.'

Set @ servername = @ servername

EXEC msdb .. sp_add_jobserver

@ Job_name = @ jobname,

@ Server_name = @ servername

Go

 

-- Call -- job executed every day

Exec p_createjob @ jobname = DD,

@ SQL = insert B select converter (char (10), getdate (), 120), 1 + (select max ([text]) from B ),

@ Servername = Name of the job server,

@ Dbname = Database Name,

@ Freqtype = day, @ time = 000000