Prev: SQL Standart server 2005 can't see my local folder [ external USB
Next: Determining T-SQL within a transaction_id
From: Mehul Patel on 12 Jul 2010 08:23
Below is the manual SQL fix to address this error:

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)

Here is the SQL SCRIPT to fix this:

NOTE: Replace �MaintenancePlan� with the value in the Jobs under SQL Server Agent, put the job name

=========
USE [msdb]
declare @job_name varchar(100)
set @job_name = 'CRS Daily Maintenance.Subplan_1'

-- First, delete the logs for the plan

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 the Subplan

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)

-- Delete the actual job (You can do the same thing through Management Studio (Enterprise Manager)

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

==========
Above Script will fetch Job ID for maintenance plan and Sub Maintenance Plan from the system.
==========

If you get this error:

The DELETE statement conflicted with the REFERENCE constraint "FK__sysjobsch__job_i__276EDEB3".
The conflict occurred in database "msdb", table "dbo.sysjobschedules", column 'job_id'.

Open the Maintenance Sub Job Plan Under SQL JObs Agent and Delete the Schedules and Run the Above Query Again � It will Delete the Maintenance & Sub Maintenance Job
- You can create new Maintenance Plan As you require.

From http://www.developmentnow.com/g/118_2006_10_0_0_831628/Cannot-Delete-Maintenance-Plans--SQL-2005.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/
From: Dan on 12 Jul 2010 09:06

"Mehul Patel" <mehul.210184(a)gmail.com> wrote in message
news:e79d1ede-a289-4b02-9eaa-5337ad285b7f(a)developmentnow.com...
> Below is the manual SQL fix to address this error:

Why post an answer to a topic from May 2006! Do you think the OP is still
waiting for an answer 4 years later???

--
Dan

 | 
Pages: 1
Prev: SQL Standart server 2005 can't see my local folder [ external USB
Next: Determining T-SQL within a transaction_id