This was found in a post on csdn. I used it and turned it around.
Libin_ftsafe)
Create a job using a script:
Bytes -------------------------------------------------------------------------------------
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 (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
-- Create a job
Exec MSDB .. sp_add_jobstep @ job_name = @ jobname,
@ Step_name = 'data ',
@ 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
-- Daily jobs
Exec p_createjob @ jobname = 'dd'
@ SQL = 'insert B select convert (char (10), getdate (), 120), 1 + (select max ([text]) from B )'
, @ Servername = 'job server name'
, @ Dbname = 'database name'
, @ Freqtype = 'day'
, @ Time = '000000'
========================================================== ======================================
The following is another version from the blog
Http://www.cnblogs.com/datasky/archive/2006/12/28/606354.html
-- Jobs executed every month
Exec p_createjob @ jobname = 'mm', @ SQL = 'select * From syscolumns ', @ freqtype = 'month'
-- Weekly jobs
Exec p_createjob @ jobname = 'ww ', @ SQL = 'select * From syscolumns', @ freqtype = 'Week'
-- Daily jobs
Exec p_createjob @ jobname = 'A', @ SQL = 'select * From syscolumns'
-- Jobs executed daily and repeated every four hours every day
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 (100), -- job name
@ SQL varchar (8000), -- command to be executed
@ 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
-- Create a job
Exec MSDB .. sp_add_jobstep @ job_name = @ jobname,
@ Step_name = 'data ',
@ 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
-- Add the target server
Exec MSDB. DBO. sp_add_jobserver
@ Job_name = @ jobname,
@ SERVER_NAME = n' (local )'
Go