用sql语句创建sql job代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】用sql语句创建sql job

作者:步帝 / 发布于2015/3/24/ 861

注：用 YOUR_SQL_JOB_NAME 替换成真实的 sql job name 运行环境：sql server 2005, 2008, 2008 R2, 2010, 2012


	USE [msdb]
	GO
	/****** Object: Job [YOUR_SQL_JOB_NAME] Script Date: 2/20/2014 4:39:52 AM ******/
	IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'YOUR_SQL_JOB_NAME')
	EXEC msdb.dbo.sp_delete_job @job_name=N'YOUR_SQL_JOB_NAME', @delete_unused_schedule=1
	GO
	/****** Object: Job [YOUR_SQL_JOB_NAME] Script Date: 2/20/2014 4:39:52 AM ******/
	BEGIN TRANSACTION
	DECLARE @ReturnCode INT
	SELECT @ReturnCode = 0
	/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 2/20/2014 4:39:52 AM ******/
	IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
	BEGIN
	EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	END
	DECLARE @jobId BINARY(16)
	EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'YOUR_SQL_JOB_NAME',
	 @enabled=1,
	 @notify_level_eventlog=0,
	 @notify_level_email=0,
	 @notify_level_netsend=0,
	 @notify_level_page=0,
	 @delete_level=0,
	 @description=N'No description available.',
	 @category_name=N'[Uncategorized (Local)]',
	 --@owner_login_name=N'JobCreator',
	 @owner_login_name=N'sa',
	 @job_id = @jobId OUTPUT
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	/****** Object: Step [1] Script Date: 2/20/2014 4:39:53 AM ******/
	EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1',
	 @step_id=1,
	 @cmdexec_success_code=0,
	 @on_success_action=1,
	 @on_success_step_id=0,
	 @on_fail_action=2,
	 @on_fail_step_id=0,
	 @retry_attempts=0,
	 @retry_interval=0,
	 @os_run_priority=0, @subsystem=N'SSIS',
	 @command=N'/FILE "E:\SSIS\YOUR_SQL_JOB_NAME\YOUR_SQL_JOB_NAME.dtsx" /CHECKPOINTING OFF /SET "\Package.Variables[User::ParentName].Properties[Value]";YOUR_SQL_JOB_NAME /REPORTING E',
	 @database_name=N'master',
	 @flags=0
	 --,@proxy_name=N'Proxy'
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	COMMIT TRANSACTION
	GOTO EndSave
	QuitWithRollback:
	 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
	EndSave:
	GO

试试其它关键字

　job　

同语言下

可能有用的

步帝贡献的其它代码(12)