فیکس کردن Error پاک نشدن Job در Sql Server 2005

Drop failed for Job ‘XXXXXXXXXXXXX’. (Microsoft.SqlServer.Smo)
The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”.
The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.
The statement has been terminated. (Microsoft SQL Server, Error: 547)



اول لاگ های این پلن رو پاک میکنیم

USE [msdb]
delete sysmaintplan_log
FROM sysmaintplan_subplans AS subplans INNER JOIN
sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id INNER JOIN
sysmaintplan_log ON subplans.subplan_id = sysmaintplan_log.subplan_id
WHERE (syjobs.name = @job_name)


بعد زیرمحموعه هاش رو پاک میکنیم (ساب پلن)

delete sysmaintplan_subplans
FROM sysmaintplan_subplans AS subplans INNER JOIN
sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id
WHERE (syjobs.name = @job_name)


بعدم خود Job رو

delete from msdb.dbo.sysjobs_view where name = @job_name


توجه کنید @job_name نام Job ماست که باید در دستورات بالا جایگزین بشه