-----------------------------------------------------------监控job
SELECT
sjv.job_id,
name = convert(varchar(100),sjv.name),
sjv.enabled,
last_run_info =
( select top 1 convert(varchar,run_status)+'*'+convert(varchar,jh
.run_Date)+'*'+convert(varchar,jh.run_time)
from msdb.dbo.sysjobhistory jh where jh.job_id=sjv.job_id and jh.step_id=0
order by run_date desc, run_time desc )
FROM msdb.dbo.sysjobs_view sjv
EGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
— 删除同名的警报(如果有的话)。
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'taskname')
IF (@JobID IS NOT NULL)
BEGIN
-- 检查此作业是否为多重服务器作业
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- 已经存在,因而终止脚本
RAISERROR (N'无法导入作业“taskname”,因为已经有相同名称的多重服务器作业。', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- 删除[本地]作业
EXECUTE msdb.dbo.sp_delete_job @job_name = N'taskname'
SELECT @JobID = NULL
END