-
-
Al intentar eliminar un Job del SQL Server 2005 puede aparecer el siguiente mensaje de error
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)
nos indica que no se puede eliminar un job por problemas con los planes de mantenimiento.
Para poder resolver este problema y poder eliminar el Job les dejo el siguiente Script
declare @job_name varchar(500)
set @job_name = N'tujob' ;
USE [msdb]
delete sysmaintplan_log
FROM sysmaintplan_subplans AS subplan
INNER JOIN
sysjobs_view AS jobs ON subplan.job_id = jobs.job_id INNER JOIN
sysmaintplan_log ON subplan.subplan_id = sysmaintplan_log.subplan_id
WHERE (jobs.name = @job_name)
delete sysmaintplan_subplans
FROM sysmaintplan_subplans AS subplan INNER JOIN
sysjobs_view AS jobs ON subplan.job_id = jobs.job_id
WHERE (jobs.name = @job_name)
delete
from msdb.dbo.sysjobs_view where name = @job_name