Creation of the database job [timed execution Task] (reprint)

--Monthly job execution
exec p_createjob @jobname = ' mm ', @sql = ' select * from syscolumns ', @freqtype = ' month '
--Jobs performed weekly
exec p_createjob @jobname = ' ww ', @sql = ' select * from syscolumns ', @freqtype = ' Week '
--Daily job execution
exec p_createjob @jobname = ' a ', @sql = ' select * from syscolumns '
--daily jobs that are repeated every 4 hours
exec p_createjob @jobname = ' B ', @sql = ' select * from syscolumns ', @fsinterval =4
--*/
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_createjob] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_createjob]
GO
Create proc P_createjob
@jobname varchar,--Job name
@sql varchar (8000),--the command to execute
@dbname sysname= ',--implicitly considers the current database name
@freqtype varchar (6) = ' Day ',--time period, month months, week week
@fsinterval int=1,--Relative to the number of repetitions per day
@time int=170000--start execution time, for jobs that are executed repeatedly, from 0 to 23:59.
As
If IsNull (@dbname, ') = ' Set @dbname =db_name ()
--Create Job
EXEC msdb: Sp_add_job @[email protected]
--Create a job step
EXEC msdb: Sp_add_jobstep @[email protected],
@step_name = ' data processing ',
@subsystem = ' TSQL ',
@[email protected],
@command = @sql,
@retry_attempts = 5,--Retry count
@retry_interval = 5--Retry interval
--Create a schedule
declare @ftype int, @fstype int, @ffactor int
Select @ftype =case @freqtype when "Day" then 4
When the ' Week ' then 8
When the ' month ' then the 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 @[email protected],
@name = ' time schedule ',
@[email protected],--daily, 8 per week, 16 per month
@freq_interval =1,--Repeat execution times
@[email protected],--repeated execution
@[email protected],--repetition cycle
@[email protected],
@[email protected]--17:00:00 minute execution
--Add target server
EXEC Msdb.dbo.sp_add_jobserver
@job_name = @jobname,
@server_name = N ' (local) '
Go

Creation of the database job [timed execution Task] (reprint)