if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_jobset]') And ObjectProperty (id, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_jobset] Go
/ * - Timed call stored procedure
Create a job job execution in the specified time and calling the specified stored procedure is automatically deleted
- Zou Jian 2004.07 (Please keep this information) - * /
/ * - Call example
Exec p_jobset 'master.dbo.xp_cmdshell', '2004-1-1 10:30' - * / create proc p_jobset @ protcname sysname, - To call the stored procedure name of the timed call, if not in the current library, use : Library name. Osername. Stored procedure name @Job_Date dateTime - execution time (including time information) asclare @dbname sysname, @ JobName sysname, @ Date Int, @ Time Int
SELECT @ JobName = 'Time Send Job_' Cast (NewId () As Varchar (36)), @ Date = Convert (Varchar, @ Job_Date, 112), @ Time = Replace (Convert (varchar, @ Job_Date, 108) , ':', '')
IF exists (SELECT 1 from msdb..sysjobs where name = @ JobName) EXEC MSDB..SP_DELETE_JOB @ Job_Name = @ JobName
- Create Jobs EXEC MSDB..SP_ADD_JOB @ Job_Name = @ JobName, @ delete_level = 1
- Create Job Step Declare @SQL VARCHAR (800) SELECT @ SQL = 'Exec' @ prolcname, @ DBNAME = DB_NAME ()
exec msdb..sp_add_jobstep @ job_name = @ jobname, @step_name = 'transmission processing', @subsystem = 'TSQL', @ database_name = @ dbname, @command = @sql, @retry_attempts = 5, - the number of retries @ Retry_interval = 5 - Retry Interval
- Create schedule EXEC MSDB..SP_ADD_JOBSCHEDULE @Job_Name = @JobName, @name = 'Time Arrangers', @enabled = 1, @freq_type = 1, @Active_start_date = @Date, @Active_start_time = @time
- Add Target Server EXEC MSDB.DBO.SP_ADD_JOBSERVER @JOB_NAME = @JobName, @server_name = n '(local)' Go
