发一段自动生成JOB代码的脚本，望各位兄弟一起帮忙测试一下。（环境Sql 2005）
/*
举例:
EXEC #usp_AutoGetJobScript @Route='x:\JobTest\',@FileName='JobTest20090909.sql'
*/
CREATE PROC #usp_AutoGetJobScript
@JobName VARCHAR(256)='' --支持单个Job的脚本获取，默认为全选
,@Enable INT=2 --0表示禁用的Job; 1表示启用的Job; 2为默认，表示全选
,@Route VARCHAR(500) --输入文件路径 / 例如:e:\sqldata\ /
,@FileName VARCHAR(200)='Job.sql' --输入文件名称
AS
DECLARE @JobList TABLE(
ID INT IDENTITY(1,1) PRIMARY KEY
,JobName VARCHAR(256)
)
DECLARE @JobScript VARCHAR(MAX)
SELECT @JobScript=''
CREATE TABLE ##JobScript(SqlCmd VARCHAR(MAX))
--条件过滤
IF (@JobName='')
BEGIN
IF (@Enable NOT IN (0,1))
BEGIN
INSERT INTO @JobList
SELECT [name] FROM Msdb.dbo.sysjobs (NOLOCK)
ORDER BY [name] ASC
END
ELSE
BEGIN
INSERT INTO @JobList
SELECT [name] FROM Msdb.dbo.sysjobs (NOLOCK)
WHERE [Enabled]=@Enable
ORDER BY [name] ASC
END
END
ELSE
BEGIN
IF (@Enable NOT IN (0,1))
BEGIN
INSERT INTO @JobList
SELECT [name] FROM Msdb.dbo.sysjobs (NOLOCK)
WHERE [Name]=@JobName
ORDER BY [name] ASC
END
ELSE
BEGIN
INSERT INTO @JobList
SELECT [name] FROM Msdb.dbo.sysjobs (NOLOCK)
WHERE [Enabled]=@Enable
AND [Name]=@JobName
-- ORDER BY [name] ASC
END
END
DECLARE @ID INT
,@MaxID INT
SELECT @ID=1
SELECT @MaxID=MAX(ID)FROM @JobList
WHILE (@ID<
=@MaxID)
BEGIN
DECLARE @SqlCmd VARCHAR(MAX)
,@SqlJob VARCHAR(MAX)
,@SqlJobstep VARCHAR(MAX)
,@SqlJobstartstep VARCHAR(MAX)
,@Sqljobschedule VARCHAR(MAX)
,@Sqljobserver VARCHAR(MAX)
,@Job_id UNIQUEIDENTIFIER
,@start_step_id INT
,@categoryName VARCHAR(128)
,@category_class INT
,@ScriptTime VARCHAR(32)
SELECT @JobName=JobName FROM @JobList WHERE
ID=@ID SELECT @ScriptTime=CONVERT(VARCHAR(19),GETDATE(),101)+' '+CONVERT(VARCHAR(8),GETDATE(),114)
SELECT TOP 1 @categoryName=b.Name
,@category_class=b.category_class
,@Job_id=a.Job_id
,@start_step_id=start_step_id
FROM Msdb.dbo.sysjobs (NOLOCK) a
INNER JOIN Msdb.dbo.syscategories (NOLOCK) b ON a.category_id=b.category_id
WHERE
a.name=@JobName --获取Job描述信息
SELECT @SqlCmd= CHAR(10)
+'/****** Object: Job ['+@JobName+'] Script Date: [email=]'+@ScriptTime+'[/email] ******/'+CHAR(10)
+'BEGIN TRANSACTION'+CHAR(10)+'DECLARE @ReturnCode INT'+CHAR(10)+'SELECT @ReturnCode = 0'+CHAR(10)
+'/****** Object: JobCategory ['+@categoryName+'] Script Date: [email=]'+@ScriptTime+'[/email] ******/'+CHAR(10)
+'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE
name=N'''+@categoryName+''' AND category_class='+CAST(@category_class AS VARCHAR)+')'+CHAR(10)
+'BEGIN'+CHAR(10)
+'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name=N'''+@categoryName+''''+CHAR(10)
+'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'+CHAR(10)+CHAR(10)
+'END'+CHAR(10)+CHAR(10)
--获取Job基本信息
SELECT @SqlJob= 'DECLARE @jobId BINARY(16)'+CHAR(10)
+'EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'''+@JobName+''','+CHAR(10)
+' @enabled='+CAST([enabled] AS VARCHAR)+','+CHAR(10)
+' @notify_level_eventlog='+CAST(notify_level_eventlog AS VARCHAR)+','+CHAR(10)
+' @notify_level_email='+CAST(notify_level_email AS VARCHAR)+','+CHAR(10)
+' @notify_level_netsend='+CAST(notify_level_netsend AS VARCHAR)+','+CHAR(10)
+' @notify_level_page='+CAST(notify_level_page AS VARCHAR)+','+CHAR(10)
+' @delete_level='+CAST(delete_level AS VARCHAR)+','+CHAR(10)
+' @description=N'''+ISNULL([description],'')+''','+CHAR(10)
+' @category_name=N'''+@categoryName+''','+CHAR(10)
+' @owner_login_name=N'''+ISNULL(b.name,'sa')+''', @job_id = @jobId OUTPUT'+CHAR(10)
+'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
FROM Msdb.dbo.sysjobs (NOLOCK) a
LEFT JOIN SYS.SYSLOGINS (NOLOCK) b ON a.owner_sid=b.sid
WHERE
a.name=@JobName --获取Job的步骤
SELECT @SqlJobstep=''
DECLARE @step_id INT
,@step_maxid INT
SELECT @step_id=MIN(b.step_id)
,@step_maxid=MAX(b.step_id)
FROM Msdb.dbo.sysjobs (NOLOCK) a
INNER JOIN Msdb.dbo.sysjobsteps (NOLOCK) b ON a.job_id=b.job_id
WHERE
a.name=@JobName WHILE (@step_id<
=@step_maxid)
BEGIN
SELECT @SqlJobstep= @SqlJobstep+CHAR(10)
+'/****** Object: Step ['+step_name+'] Script Date: [email=]'+@ScriptTime+'[/email] ******/'+CHAR(10)
+'EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'''+step_name+''','+CHAR(10)
+' @step_id='+CAST(step_id AS VARCHAR)+','+CHAR(10)
+' @cmdexec_success_code='+CAST(cmdexec_success_code AS VARCHAR)+','+CHAR(10)
+' @on_success_action='+CAST(on_success_action AS VARCHAR)+','+CHAR(10)
+' @on_success_step_id='+CAST(on_success_step_id AS VARCHAR)+','+CHAR(10)
+' @on_fail_action='+CAST(on_fail_action AS VARCHAR)+','+CHAR(10)
+' @on_fail_step_id='+CAST(on_fail_step_id AS VARCHAR)+','+CHAR(10)
+' @retry_attempts='+CAST(retry_attempts AS VARCHAR)+','+CHAR(10)
+' @retry_interval='+CAST(retry_interval AS VARCHAR)+','+CHAR(10)
+' @os_run_priority='+CAST(os_run_priority AS VARCHAR)+','
+' @subsystem=N'''+subsystem+''','+CHAR(10)
+' @command=N'''+ISNULL(REPLACE(command,'''',''''''),'')+''','+CHAR(10)
+ISNULL(' @server=N'''+REPLACE([server],'''','''''')+''','+CHAR(10),'')
+ISNULL(' @database_name=N'''+database_name+''','+CHAR(10),'')
+' @flags='+CAST(flags AS VARCHAR)+CHAR(10)
+'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
FROM Msdb.dbo.sysjobsteps (NOLOCK)
WHERE
Job_id=@Job_id AND
step_id=@step_id SELECT @step_id=MIN(step_id)FROM Msdb.dbo.sysjobsteps (NOLOCK)WHERE
Job_id=@Job_id AND step_id>@step_id
END
--获取Job开始步骤
SELECT @SqlJobstartstep=CHAR(10)+'EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id ='+CAST(@start_step_id AS VARCHAR)
+CHAR(10)+'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'+CHAR(10)
--获取Job的调度
SELECT @Sqljobschedule=''
SELECT @Sqljobschedule= @Sqljobschedule+CHAR(10)
+'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'''+b.name+''','+CHAR(10)
+' @enabled='+CAST(b.enabled AS VARCHAR)+','+CHAR(10)
+' @freq_type='+CAST(b.freq_type AS VARCHAR)+','+CHAR(10)
+' @freq_interval='+CAST(b.freq_interval AS VARCHAR)+','+CHAR(10)
+' @freq_subday_type='+CAST(b.freq_subday_type AS VARCHAR)+','+CHAR(10)
+' @freq_subday_interval='+CAST(b.freq_subday_interval AS VARCHAR)+','+CHAR(10)
+' @freq_relative_interval='+CAST(b.freq_relative_interval AS VARCHAR)+','+CHAR(10)
+' @freq_recurrence_factor='+CAST(b.freq_recurrence_factor AS VARCHAR)+','+CHAR(10)
+' @active_start_date='+CAST(b.active_start_date AS VARCHAR)+','+CHAR(10)
+' @active_end_date='+CAST(b.active_end_date AS VARCHAR)+','+CHAR(10)
+' @active_start_time='+CAST(b.active_start_time AS VARCHAR)+','+CHAR(10)
+' @active_end_time='+CAST(b.active_end_time AS VARCHAR)+CHAR(10)
+'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
FROM Msdb.dbo.sysjobs (NOLOCK) a
INNER JOIN Msdb.dbo.sysjobschedules (NOLOCK) c ON a.job_id=c.job_id
INNER JOIN Msdb.dbo.sysschedules (NOLOCK) b ON c.schedule_id=b.schedule_id
WHERE
a.name=@JobName ORDER BY b.schedule_id ASC
--获取Jobserver
SELECT @Sqljobserver= CHAR(10)+'EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'''
+CASE WHEN a.originating_server_id=0 THEN '(local)' ELSE b.name END+''''+CHAR(10)
+'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'+CHAR(10)
+'COMMIT TRANSACTION'+CHAR(10)
+'GOTO EndSave'+CHAR(10)
+'QuitWithRollback:'+CHAR(10)
+' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'+CHAR(10)
+'EndSave:'
FROM Msdb.dbo.sysjobs (NOLOCK) a
INNER JOIN sys.servers (NOLOCK) b ON a.originating_server_id=b.server_id
SELECT @JobScript= @JobScript
+CHAR(10)
+ISNULL(@SqlCmd,'')
+ISNULL(@SqlJob,'')
+ISNULL(@SqlJobstep,'')
+ISNULL(@SqlJobstartstep,'')
+ISNULL(@Sqljobschedule,'')
+ISNULL(@Sqljobserver ,'')
+CHAR(10)+'GO'
SELECT @ID=@ID+1
END
SELECT @JobScript='USE [msdb]'+CHAR(10)+'GO'+@JobScript
INSERT INTO ##JobScript
SELECT @JobScript
--导出到文件
DECLARE @SqlText VARCHAR(MAX)
SELECT @SqlText='Master.dbo.XP_CMDSHELL ''BCP ##JobScript OUT [email=]'+@Route+@FileName+'[/email] -T -c
-S'+@@SERVERNAME+''''EXEC (@SqlText)
DROP TABLE ##JobScript
