删除本数据库所有作业(jobs)的T-SQL语句.
我的做法
--Delect all jobs in this server!--
use msdb
declare jobs_cursor cursor
for
select job_id from msdb.dbo.sysjobservers
open jobs_cursor
declare @t_job_id uniqueidentifier
fetch next from jobs_cursor into @t_job_id
WHILE (@@fetch_status <> -1)
BEGIN
EXEC sp_delete_job @[email protected]_job_id
fetch next from jobs_cursor into @t_job_id
END
deallocate jobs_cursor
------解决方案--------------------
好像只能向lz这样做
------解决方案--------------------
--或者
declare @sql varchar(8000)
Select @sql=isnull(@sql, ' ')+ 'EXEC msdb.dbo.sp_delete_job ' ' '+rtrim(job_id)
+ ' ' ' '+Char(13)from msdb.dbo.sysjobservers
exec(@sql)
------解决方案--------------------
sp_delete_job 是这样删除的。
-------
...
INSERT INTO #temp_jobs_to_delete
SELECT job_id, (SELECT COUNT(*)
FROM msdb.dbo.sysjobservers
WHERE (job_id = @job_id)
AND (server_id = 0))
FROM msdb.dbo.sysjobs_view
WHERE (job_id = @job_id)
.....
-- Delete all traces of the job
BEGIN TRANSACTION
DELETE FROM msdb.dbo.sysjobs
WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)
DELETE FROM msdb.dbo.sysjobservers
WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)
DELETE FROM msdb.dbo.sysjobsteps
WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)
DELETE FROM msdb.dbo.sysjobschedules
WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)
IF (@delete_history = 1)
DELETE FROM msdb.dbo.sysjobhistory
WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)
COMMIT TRANSACTION
------解决方案--------------------
学习 接分
------解决方案--------------------
不懂 友情UP