以前mssql2000中,能一次性到处所有作业的脚本,现在使用mssql2008,怎么在面板中到处所有作业的脚本呢?请高手指点
解决方案 »
- sql关于行号的一个比较烦人的问题
- 想统计出下表中的年级字段下的记录中“初”出现的次数,该如何编写的sql语句
- 一个关于触发器的问题
- 这样你们是如何处理的?
- 难道又要重装系统?
- 自动增长
- select count(*) from A;select count(*) from B;然后把这两个sql返回的结果相乘,如果用一个sql表示,怎么写啊?
- 触发器,表一旦保存失败,就报 缺少相应的begin tran!!!为什么???如何修改???
- 关于在sql server7.0中的建库问题,大虾们请进来一看。
- 请教:关于Borland c++3.1编写访问mssql6.5数据库的问题
- 怎样实现最大值相加?
- sql server 2008图形界面中的问题
create proc Generate_Job_Script_for2008
as
set nocount on
declare @jobname nvarchar(1000)
declare @tb table (id int identity(1,1),sqltext varchar(4000))
declare @job_category_name varchar(1000)
declare @job_id varchar(100)
declare @changeline varchar(10)
declare @enabled int,
@notify_level_eventlog int,
@notify_level_email int,
@notify_level_netsend int ,
@notify_level_page int,
@delete_level int,
@description varchar(1000),
@category_name varchar(1000),
@owner_login_name varchar(1000)declare c cursor for
select name from msdb.dbo.sysjobs
open c
fetch c into @jobname
while @@FETCH_STATUS=0
begin
set @changeline = ','+char(13)
select @job_category_name = replace(b.name,'''',''''''),@job_id = a.job_id
from msdb.dbo.sysjobs a join msdb.dbo.syscategories b on a.category_id = b.category_id where a.name =@jobname insert into @tb(sqltext)
select 'USE [msdb]' union all
select 'GO' union all
select '/****** 对象: Job ['+@jobname+'] 脚本日期: '+convert(varchar(10),getdate(),101)+' '+convert(varchar(8),getdate(),108)+' ******/' union all
select 'BEGIN TRANSACTION' union all
select 'DECLARE @ReturnCode INT' union all
select 'SELECT @ReturnCode = 0' union all
select '/****** 对象: JobCategory ['+@job_category_name+'] 脚本日期: '+convert(varchar(10),getdate(),101)+' '+convert(varchar(8),getdate(),108)+' ******/' union all
select 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'''+@job_category_name+''' AND category_class=1)' union all
select 'BEGIN' union all
select 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name=N'''+@job_category_name+'''' union all
select 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' union all
select '' union all
select 'END' select
@enabled = enabled,
@notify_level_eventlog =notify_level_eventlog,
@notify_level_email =notify_level_email,
@notify_level_netsend =notify_level_netsend,
@notify_level_page =notify_level_page,
@delete_level =delete_level,
@description =description,
@category_name =@job_category_name,
@owner_login_name =suser_sname(owner_sid)
from msdb.dbo.sysjobs
where job_id =@job_id insert into @tb(sqltext)
select 'DECLARE @jobId BINARY(16)' union all
select 'EXEC @ReturnCode = msdb.dbo.sp_add_job ' union all
select ' @job_name=N'''+@jobname+''', ' union all
select ' @enabled='+isnull(convert(varchar(10),@enabled),'')+', ' union all
select ' @notify_level_eventlog='+isnull(convert(varchar(10),@notify_level_eventlog),'')+', ' union all
select ' @notify_level_email='+isnull(convert(varchar(10),@notify_level_email),'')+', ' union all
select ' @notify_level_netsend='+isnull(convert(varchar(10),@notify_level_netsend),'')+', ' union all
select ' @notify_level_page='+isnull(convert(varchar(10),@notify_level_page),'')+', ' union all
select ' @delete_level='+isnull(convert(varchar(10),@delete_level),'')+', ' union all
select ' @description=N'''+isnull(convert(varchar(1000),replace(@description,'''','''''')),'')+''', ' union all
select ' @category_name=N'''+isnull(convert(varchar(1000),replace(@category_name,'''','''''')),'')+''', ' union all
select ' @owner_login_name=N'''+isnull(convert(varchar(100),replace(@owner_login_name,'''','''''')),'')+''', @job_id = @jobId OUTPUT' union all
select 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' if exists( select 1 from msdb.dbo.sysjobsteps where job_id =@job_id)
begin
insert into @tb(sqltext)
select c1 from (
select 1 as id,step_id,
'/****** 对象: Step ['+convert(varchar(10),step_id )+'] 脚本日期: '+convert(varchar(10),getdate(),101)+' '+convert(varchar(8),getdate(),108)+' ******/'+char(13)+
'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep '+replace(@changeline,',','') +
' @job_id = @JobID'+@changeline+
' @step_name = N'''+isnull(replace(step_name,'''','''''') ,'')+''''+@changeline+
' @step_id = '+isnull(convert(varchar(10),step_id ),'') + @changeline+
' @cmdexec_success_code = '+isnull(convert(varchar(10),cmdexec_success_code ),'')+ @changeline+
' @on_success_action = '+isnull(convert(varchar(10),on_success_action ),'') + @changeline+
' @on_success_step_id = '+isnull(convert(varchar(10),on_success_step_id ),'') + @changeline+
' @on_fail_action = '+isnull(convert(varchar(10),on_fail_action ),'') + @changeline+
' @on_fail_step_id = '+isnull(convert(varchar(10),on_fail_step_id ),'') + @changeline+
' @retry_attempts = '+isnull(convert(varchar(10),retry_attempts ),'') + @changeline+
' @retry_interval = '+isnull(convert(varchar(10),retry_interval ),'') + @changeline+
' @os_run_priority = '+isnull(convert(varchar(10),os_run_priority ),'') + @changeline+
' @subsystem = N'''+isnull(convert(varchar(100),replace(subsystem,'''','''''') ),'') +''''+ @changeline+
' @command = N'''+isnull(convert(varchar(8000),replace(command,'''','''''') ),'') +''''+ @changeline+
' @database_name = N'''+isnull(convert(varchar(100),replace(database_name,'''','''''') ),'') +''''+ @changeline+
' @flags = '+isnull(convert(varchar(10),flags ),'') + replace(@changeline,',','') as c1 from msdb.dbo.sysjobsteps
where job_id =@job_id union all select 2 as id,step_id, ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ' as c1 from msdb.dbo.sysjobsteps
where job_id =@job_id
) abc
order by step_id,id end insert into @tb(sqltext)
select ' EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = '+isnull(convert(varchar(100),start_step_id),'')+' '
from msdb.dbo.sysjobs
where job_id =@job_id union all
select '' union all
select ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback '
insert into @tb(sqltext)
select '' if exists (select 1 from msdb.dbo.sysjobschedules where job_id =@job_id)
begin
-- insert into @tb(sqltext)
-- select ' -- 添加作业调度' insert into @tb(sqltext)
select c1 from (
select 1 as id,a.schedule_id,
'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule '+replace(@changeline,',','') +
' @job_id = @JobID'+@changeline+
' @name = N'''+isnull(replace(name,'''','''''') ,'')+''''+@changeline+
' @enabled = '+isnull(convert(varchar(10),enabled ),'') + @changeline+
' @freq_type = '+isnull(convert(varchar(10),freq_type ),'')+ @changeline+
' @freq_interval = '+isnull(convert(varchar(10),freq_interval ),'') + @changeline+
' @freq_subday_type = '+isnull(convert(varchar(10),freq_subday_type ),'') + @changeline+
' @freq_subday_interval = '+isnull(convert(varchar(10),freq_subday_interval ),'') + @changeline+
' @freq_relative_interval = '+isnull(convert(varchar(10),freq_relative_interval ),'') + @changeline+
' @freq_recurrence_factor = '+isnull(convert(varchar(10),freq_recurrence_factor ),'') + @changeline+
' @active_start_date = '+isnull(convert(varchar(100),active_start_date ),'') + @changeline+
' @active_end_date = '+isnull(convert(varchar(100),active_end_date ),'') + @changeline+
' @active_start_time = '+isnull(convert(varchar(100),active_start_time ),'') + @changeline+
' @active_end_time = '+isnull(convert(varchar(100),active_end_time ),'') + replace(@changeline,',','')
as c1
from msdb.dbo.sysschedules a join msdb.dbo.sysjobschedules b
on a.schedule_id = b.schedule_id
where b.job_id =@job_id union all select 2 as id,schedule_id,' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ' as c1
from msdb.dbo.sysjobschedules
where job_id =@job_id
) aa
order by schedule_id,id end insert into @tb(sqltext)
--select ' -- 添加目标服务器' union all
select
' EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'''+case when a.server_id = 0 then '(local)' else b.srvname end +''' '
from msdb.dbo.sysjobservers a join master.dbo.sysservers b
on a.server_id = b.srvid
where job_id =@job_id
union all
select ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ' insert into @tb(sqltext)
select 'COMMIT TRANSACTION' union all
select 'GOTO EndSave' union all
select 'QuitWithRollback:' union all
select ' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION' union all
select 'EndSave:'
fetch c into @jobname
end
close c
deallocate c
select sqltext from @tb
GO
--原脚本参考:http://hi.baidu.com/jixj/blog/item/5b5f07ec0212abd3b21cb131.html
--调用:
exec Generate_Job_Script_for2008