Cannot edit maintenance plans post SP2

I have some maintenance plans written for SQL Server 2005 SP1. After we
upgraded to SP2 I can no longer edit them. If I select the Maintneance Plans
node in Object Explorer and double click on any plan I get "Microsoft SQL
Server Management Studio is unable to load this document: One or more error
occured."
The popup message box says "Value cannot be null. Parameter name: component
(System.Design)"
Details are:"
Program Location:
at
System.ComponentModel.Design.DesignerHost.System.C omponentModel.Design.IDesignerHost.GetDesigner(ICo mponent component)
at
Microsoft.DataTransformationServices.VsIntegration .DtsDesignerService.OpenDesigner(String connectionString, Object connection, String objectUrn)
at
Microsoft.SqlServer.Management.DatabaseMaintenance .MaintDesignerMenuHandler.Invoke()"
I saw one post that sugget there is a fix that ought to be in cumulative
update package 4, but I can't see any items in that which match this problem.
The maintenance plans appear to run when called by the SQl Agent Jobs they
were initially set up with. However, I am calling one of them from another
Job and this has started throwing this error since SP2:
"Error: 2007-11-04 18:00:01.81 Code: 0xC0024104 Source: Reporting
Task for subplan-{03E683E8-5A40-4697-BF2F-E93EBA6E96E7} Description: The
Execute method on the task returned error code 0x80131904 (Conversion failed
when converting from a character string to uniqueidentifier.). The Execute
method must succeed<c/> and indicate the result using an "out" parameter.
End Error"
but when I went to look at the plan, I couldn't edit it!
Any advice on how to edit the maintenance plans or fix the second error
would be great.
Thanks,
Charles
Yes. From Help -> about:
"Microsoft SQL Server Management Studio9.00.3042.00
Microsoft Analysis Services Client Tools2005.090.3042.00
Microsoft Data Access Components (MDAC)2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer6.0.2900.2180
Microsoft .NET Framework2.0.50727.832
Operating System5.1.2600"
"Tibor Karaszi" wrote:

> Did you install sp2 for the clients components (SSMS) on the machine where you try to edit the plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "CharlesB1" <CharlesB1@.discussions.microsoft.com> wrote in message
> news:CC70B322-EBCB-4D2E-B1DA-98AAEDDBF86A@.microsoft.com...
>
>
sql

Cannot edit maintenance plans post SP2

I have some maintenance plans written for SQL Server 2005 SP1. After we
upgraded to SP2 I can no longer edit them. If I select the Maintneance Plans
node in Object Explorer and double click on any plan I get "Microsoft SQL
Server Management Studio is unable to load this document: One or more error
occured."
The popup message box says "Value cannot be null. Parameter name: component
(System.Design)"
Details are:"
Program Location:
at
System.ComponentModel.Design.DesignerHost.System.ComponentModel.Design.IDesi
gnerHost.GetDesigner(IComponent component)
at
Microsoft.DataTransformationServices.VsIntegration.DtsDesignerService.OpenDe
signer(String connectionString, Object connection, String objectUrn)
at
Microsoft.SqlServer.Management.DatabaseMaintenance.MaintDesignerMenuHandler.
Invoke()"
I saw one post that sugget there is a fix that ought to be in cumulative
update package 4, but I can't see any items in that which match this problem
.
The maintenance plans appear to run when called by the SQl Agent Jobs they
were initially set up with. However, I am calling one of them from another
Job and this has started throwing this error since SP2:
"Error: 2007-11-04 18:00:01.81 Code: 0xC0024104 Source: Reporting
Task for subplan-{03E683E8-5A40-4697-BF2F-E93EBA6E96E7} Descriptio
n: The
Execute method on the task returned error code 0x80131904 (Conversion failed
when converting from a character string to uniqueidentifier.). The Execute
method must succeed<c/> and indicate the result using an "out" parameter.
End Error"
but when I went to look at the plan, I couldn't edit it!
Any advice on how to edit the maintenance plans or fix the second error
would be great.
Thanks,
CharlesDid you install sp2 for the clients components (SSMS) on the machine where y
ou try to edit the plan?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"CharlesB1" <CharlesB1@.discussions.microsoft.com> wrote in message
news:CC70B322-EBCB-4D2E-B1DA-98AAEDDBF86A@.microsoft.com...
>I have some maintenance plans written for SQL Server 2005 SP1. After we
> upgraded to SP2 I can no longer edit them. If I select the Maintneance Pla
ns
> node in Object Explorer and double click on any plan I get "Microsoft SQL
> Server Management Studio is unable to load this document: One or more erro
r
> occured."
> The popup message box says "Value cannot be null. Parameter name: componen
t
> (System.Design)"
> Details are:"
> Program Location:
> at
> System.ComponentModel.Design.DesignerHost.System.ComponentModel.Design.IDe
signerHost.GetDesigner(IComponent
> component)
> at
> Microsoft.DataTransformationServices.VsIntegration.DtsDesignerService.Open
Designer(String
> connectionString, Object connection, String objectUrn)
> at
> Microsoft.SqlServer.Management.DatabaseMaintenance.MaintDesignerMenuHandle
r.Invoke()"
> I saw one post that sugget there is a fix that ought to be in cumulative
> update package 4, but I can't see any items in that which match this probl
em.
> The maintenance plans appear to run when called by the SQl Agent Jobs they
> were initially set up with. However, I am calling one of them from another
> Job and this has started throwing this error since SP2:
> "Error: 2007-11-04 18:00:01.81 Code: 0xC0024104 Source: Reporting
> Task for subplan-{03E683E8-5A40-4697-BF2F-E93EBA6E96E7} Descript
ion: The
> Execute method on the task returned error code 0x80131904 (Conversion fail
ed
> when converting from a character string to uniqueidentifier.). The Execute
> method must succeed<c/> and indicate the result using an "out" parameter.
> End Error"
> but when I went to look at the plan, I couldn't edit it!
> Any advice on how to edit the maintenance plans or fix the second error
> would be great.
> Thanks,
> Charles
>|||Yes. From Help -> about :
"Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.832
Operating System 5.1.2600"
"Tibor Karaszi" wrote:

> Did you install sp2 for the clients components (SSMS) on the machine where
you try to edit the plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "CharlesB1" <CharlesB1@.discussions.microsoft.com> wrote in message
> news:CC70B322-EBCB-4D2E-B1DA-98AAEDDBF86A@.microsoft.com...
>
>

Cannot edit maintenance plans post SP2

I have some maintenance plans written for SQL Server 2005 SP1. After we
upgraded to SP2 I can no longer edit them. If I select the Maintneance Plans
node in Object Explorer and double click on any plan I get "Microsoft SQL
Server Management Studio is unable to load this document: One or more error
occured."
The popup message box says "Value cannot be null. Parameter name: component
(System.Design)"
Details are:"
Program Location:
at
System.ComponentModel.Design.DesignerHost.System.ComponentModel.Design.IDesignerHost.GetDesigner(IComponent component)
at
Microsoft.DataTransformationServices.VsIntegration.DtsDesignerService.OpenDesigner(String connectionString, Object connection, String objectUrn)
at
Microsoft.SqlServer.Management.DatabaseMaintenance.MaintDesignerMenuHandler.Invoke()"
I saw one post that sugget there is a fix that ought to be in cumulative
update package 4, but I can't see any items in that which match this problem.
The maintenance plans appear to run when called by the SQl Agent Jobs they
were initially set up with. However, I am calling one of them from another
Job and this has started throwing this error since SP2:
"Error: 2007-11-04 18:00:01.81 Code: 0xC0024104 Source: Reporting
Task for subplan-{03E683E8-5A40-4697-BF2F-E93EBA6E96E7} Description: The
Execute method on the task returned error code 0x80131904 (Conversion failed
when converting from a character string to uniqueidentifier.). The Execute
method must succeed<c/> and indicate the result using an "out" parameter.
End Error"
but when I went to look at the plan, I couldn't edit it!
Any advice on how to edit the maintenance plans or fix the second error
would be great.
Thanks,
CharlesDid you install sp2 for the clients components (SSMS) on the machine where you try to edit the plan?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"CharlesB1" <CharlesB1@.discussions.microsoft.com> wrote in message
news:CC70B322-EBCB-4D2E-B1DA-98AAEDDBF86A@.microsoft.com...
>I have some maintenance plans written for SQL Server 2005 SP1. After we
> upgraded to SP2 I can no longer edit them. If I select the Maintneance Plans
> node in Object Explorer and double click on any plan I get "Microsoft SQL
> Server Management Studio is unable to load this document: One or more error
> occured."
> The popup message box says "Value cannot be null. Parameter name: component
> (System.Design)"
> Details are:"
> Program Location:
> at
> System.ComponentModel.Design.DesignerHost.System.ComponentModel.Design.IDesignerHost.GetDesigner(IComponent
> component)
> at
> Microsoft.DataTransformationServices.VsIntegration.DtsDesignerService.OpenDesigner(String
> connectionString, Object connection, String objectUrn)
> at
> Microsoft.SqlServer.Management.DatabaseMaintenance.MaintDesignerMenuHandler.Invoke()"
> I saw one post that sugget there is a fix that ought to be in cumulative
> update package 4, but I can't see any items in that which match this problem.
> The maintenance plans appear to run when called by the SQl Agent Jobs they
> were initially set up with. However, I am calling one of them from another
> Job and this has started throwing this error since SP2:
> "Error: 2007-11-04 18:00:01.81 Code: 0xC0024104 Source: Reporting
> Task for subplan-{03E683E8-5A40-4697-BF2F-E93EBA6E96E7} Description: The
> Execute method on the task returned error code 0x80131904 (Conversion failed
> when converting from a character string to uniqueidentifier.). The Execute
> method must succeed<c/> and indicate the result using an "out" parameter.
> End Error"
> but when I went to look at the plan, I couldn't edit it!
> Any advice on how to edit the maintenance plans or fix the second error
> would be great.
> Thanks,
> Charles
>|||Yes. From Help -> About:
"Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.832
Operating System 5.1.2600"
"Tibor Karaszi" wrote:
> Did you install sp2 for the clients components (SSMS) on the machine where you try to edit the plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "CharlesB1" <CharlesB1@.discussions.microsoft.com> wrote in message
> news:CC70B322-EBCB-4D2E-B1DA-98AAEDDBF86A@.microsoft.com...
> >I have some maintenance plans written for SQL Server 2005 SP1. After we
> > upgraded to SP2 I can no longer edit them. If I select the Maintneance Plans
> > node in Object Explorer and double click on any plan I get "Microsoft SQL
> > Server Management Studio is unable to load this document: One or more error
> > occured."
> >
> > The popup message box says "Value cannot be null. Parameter name: component
> > (System.Design)"
> > Details are:"
> > Program Location:
> >
> > at
> > System.ComponentModel.Design.DesignerHost.System.ComponentModel.Design.IDesignerHost.GetDesigner(IComponent
> > component)
> > at
> > Microsoft.DataTransformationServices.VsIntegration.DtsDesignerService.OpenDesigner(String
> > connectionString, Object connection, String objectUrn)
> > at
> > Microsoft.SqlServer.Management.DatabaseMaintenance.MaintDesignerMenuHandler.Invoke()"
> >
> > I saw one post that sugget there is a fix that ought to be in cumulative
> > update package 4, but I can't see any items in that which match this problem.
> >
> > The maintenance plans appear to run when called by the SQl Agent Jobs they
> > were initially set up with. However, I am calling one of them from another
> > Job and this has started throwing this error since SP2:
> > "Error: 2007-11-04 18:00:01.81 Code: 0xC0024104 Source: Reporting
> > Task for subplan-{03E683E8-5A40-4697-BF2F-E93EBA6E96E7} Description: The
> > Execute method on the task returned error code 0x80131904 (Conversion failed
> > when converting from a character string to uniqueidentifier.). The Execute
> > method must succeed<c/> and indicate the result using an "out" parameter.
> > End Error"
> >
> > but when I went to look at the plan, I couldn't edit it!
> >
> > Any advice on how to edit the maintenance plans or fix the second error
> > would be great.
> >
> > Thanks,
> >
> > Charles
> >
>
>

Cannot edit maintenance plan created in SQL 2005, SP1

Hello,

I have created a simple maintenance plan in SQL Studio. When I double-click on the task boxes (ie 'Rebuild Index', 'Backup') to just make changes or to look, I get the following message:

Cannot show editor for this task

Additional information:

Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)

I also cannot save any change that I make in the schedule of the plan.

I HAVE installed Integration Services, which at least enabled me to initially SAVE the plan. I just can't edit the tasks or change the schedule.

Any help is most appreciated!

Eunice Griffin

I get the same thing - I finally solved all the integration services got to create a backup maint. plan now I can't edit it - what a pain.

Cannot edit maintenance plan created in SQL 2005, SP1

Hello,

I have created a simple maintenance plan in SQL Studio. When I double-click on the task boxes (ie 'Rebuild Index', 'Backup') to just make changes or to look, I get the following message:

Cannot show editor for this task

Additional information:

Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)

I also cannot save any change that I make in the schedule of the plan.

I HAVE installed Integration Services, which at least enabled me to initially SAVE the plan. I just can't edit the tasks or change the schedule.

Any help is most appreciated!

Eunice Griffin

I get the same thing - I finally solved all the integration services got to create a backup maint. plan now I can't edit it - what a pain.

Cannot Delete Maintenance Plans - SQL 2005

A consultant created a maintenance plan that isn't in use and is not
enabled. I want to delete the plan but get the following error.
TITLE: Microsoft SQL Server Management Studio
Drop failed for Job 'Maintenance II'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdN...b&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
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)
For help, click:
http://go.microsoft.com/fwlink?ProdN...7&LinkId=20476
BUTTONS:
OK
I"m such a noob, how can i delete this plan?
tia,
Randy
>From what I can tell from the error msg, the job you are trying to
delete has a subplan that references it and you can't delete it until
you get rid of the subplan.
How I would do this:
1.) get job id #
2.) look up job id# in the system table in question
3.) find the subplan for the job id #
4.) delete subplan
4.) delete job
proceed with caution and don't delete anything unless 100% sure !
you should back up your jobs first, too in case something blows up
perhaps there is an easier way to do this through the menus in
Management Studio
Randy H wrote:
> A consultant created a maintenance plan that isn't in use and is not
> enabled. I want to delete the plan but get the following error.
> TITLE: Microsoft SQL Server Management Studio
> --
> Drop failed for Job 'Maintenance II'. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...b&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> 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)
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...7&LinkId=20476
> --
> BUTTONS:
> OK
> --
> I"m such a noob, how can i delete this plan?
> tia,
> Randy
|||Is there an easier way through the menus?
I am not having any luck finding the sub plan for the job id #
<tootsuite@.gmail.com> wrote in message
news:1160078499.215907.169040@.k70g2000cwa.googlegr oups.com...
> delete has a subplan that references it and you can't delete it until
> you get rid of the subplan.
> How I would do this:
> 1.) get job id #
> 2.) look up job id# in the system table in question
> 3.) find the subplan for the job id #
> 4.) delete subplan
> 4.) delete job
> proceed with caution and don't delete anything unless 100% sure !
> you should back up your jobs first, too in case something blows up
> perhaps there is an easier way to do this through the menus in
> Management Studio
>
> Randy H wrote:
>
|||Here is the SQL to fix this:
http://www.gudenas.com/2007/04/20/sql-server-2005-delete-maintenance-plan-error/
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

Cannot Delete Maintenance Plans - SQL 2005

A consultant created a maintenance plan that isn't in use and is not
enabled. I want to delete the plan but get the following error.
TITLE: Microsoft SQL Server Management Studio
--
Drop failed for Job 'Maintenance II'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?Prod...ob&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
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)
For help, click:
http://go.microsoft.com/fwlink?Prod...47&LinkId=20476
BUTTONS:
OK
--
I"m such a noob, how can i delete this plan?
tia,
Randy>From what I can tell from the error msg, the job you are trying to
delete has a subplan that references it and you can't delete it until
you get rid of the subplan.
How I would do this:
1.) get job id #
2.) look up job id# in the system table in question
3.) find the subplan for the job id #
4.) delete subplan
4.) delete job
proceed with caution and don't delete anything unless 100% sure !
you should back up your jobs first, too in case something blows up
perhaps there is an easier way to do this through the menus in
Management Studio
Randy H wrote:
> A consultant created a maintenance plan that isn't in use and is not
> enabled. I want to delete the plan but get the following error.
> TITLE: Microsoft SQL Server Management Studio
> --
> Drop failed for Job 'Maintenance II'. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...ob&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> 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)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...47&LinkId=20476
> --
> BUTTONS:
> OK
> --
> I"m such a noob, how can i delete this plan?
> tia,
> Randy|||Is there an easier way through the menus?
I am not having any luck finding the sub plan for the job id #
<tootsuite@.gmail.com> wrote in message
news:1160078499.215907.169040@.k70g2000cwa.googlegroups.com...
> delete has a subplan that references it and you can't delete it until
> you get rid of the subplan.
> How I would do this:
> 1.) get job id #
> 2.) look up job id# in the system table in question
> 3.) find the subplan for the job id #
> 4.) delete subplan
> 4.) delete job
> proceed with caution and don't delete anything unless 100% sure !
> you should back up your jobs first, too in case something blows up
> perhaps there is an easier way to do this through the menus in
> Management Studio
>
> Randy H wrote:
>|||Here is the SQL to fix this:
http://www.gudenas.com/2007/04/20/s...s--SQL-2005.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

Cannot Delete Maintenance Plans - SQL 2005

A consultant created a maintenance plan that isn't in use and is not
enabled. I want to delete the plan but get the following error.
TITLE: Microsoft SQL Server Management Studio
--
Drop failed for Job 'Maintenance II'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476
--
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
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)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476
--
BUTTONS:
OK
--
I"m such a noob, how can i delete this plan?
tia,
Randy>From what I can tell from the error msg, the job you are trying to
delete has a subplan that references it and you can't delete it until
you get rid of the subplan.
How I would do this:
1.) get job id #
2.) look up job id# in the system table in question
3.) find the subplan for the job id #
4.) delete subplan
4.) delete job
proceed with caution and don't delete anything unless 100% sure !
you should back up your jobs first, too in case something blows up
perhaps there is an easier way to do this through the menus in
Management Studio
Randy H wrote:
> A consultant created a maintenance plan that isn't in use and is not
> enabled. I want to delete the plan but get the following error.
> TITLE: Microsoft SQL Server Management Studio
> --
> Drop failed for Job 'Maintenance II'. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> 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)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476
> --
> BUTTONS:
> OK
> --
> I"m such a noob, how can i delete this plan?
> tia,
> Randy|||Is there an easier way through the menus?
I am not having any luck finding the sub plan for the job id #
<tootsuite@.gmail.com> wrote in message
news:1160078499.215907.169040@.k70g2000cwa.googlegroups.com...
> >From what I can tell from the error msg, the job you are trying to
> delete has a subplan that references it and you can't delete it until
> you get rid of the subplan.
> How I would do this:
> 1.) get job id #
> 2.) look up job id# in the system table in question
> 3.) find the subplan for the job id #
> 4.) delete subplan
> 4.) delete job
> proceed with caution and don't delete anything unless 100% sure !
> you should back up your jobs first, too in case something blows up
> perhaps there is an easier way to do this through the menus in
> Management Studio
>
> Randy H wrote:
>> A consultant created a maintenance plan that isn't in use and is not
>> enabled. I want to delete the plan but get the following error.
>> TITLE: Microsoft SQL Server Management Studio
>> --
>> Drop failed for Job 'Maintenance II'. (Microsoft.SqlServer.Smo)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476
>> --
>> ADDITIONAL INFORMATION:
>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> 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)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476
>> --
>> BUTTONS:
>> OK
>> --
>> I"m such a noob, how can i delete this plan?
>> tia,
>> Randy
>|||Here is the SQL to fix this:
http://www.gudenas.com/2007/04/20/sql-server-2005-delete-maintenance-plan-error
From http://www.developmentnow.com/g/118_2006_10_0_0_831628/Cannot-Delete-Maintenance-Plans--SQL-2005.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comsql

Cannot delete maintenance plan.

Cannot delete a maintenance plan. SQL 2005 patched service pack 2. (That
means I was dumb enough to apply service pack 2 immediately and then
microsoft release a patch a couple days later because spk2 was broken)
The DELETE statement conflicted with the REFERENCE constraint
"FK_subplan_job_id". The conflict occurred in database "msdb", table
"dbo.sysmaintplan_subplans", column...
--Apparently the job you try to delete has one or more subplans that (for some
reason) should be deleted apart.
Try deleting the single subplans before deleting the Maintenance Plan itself.
Each Subplan has foreign keys referencing the sysjobs table, containing the
definition of the Plan.
I cannot define the exact cause of your issue because i don't know the
complete history of the plan, his subplans, the schedules etc.
Look in msdb at the system tables to undertand the relationships between the
various table but DON'T TRY to operate updates directly over them!
Gilberto Zampatti
"SQLdba" wrote:
> Cannot delete a maintenance plan. SQL 2005 patched service pack 2. (That
> means I was dumb enough to apply service pack 2 immediately and then
> microsoft release a patch a couple days later because spk2 was broken)
> The DELETE statement conflicted with the REFERENCE constraint
> "FK_subplan_job_id". The conflict occurred in database "msdb", table
> "dbo.sysmaintplan_subplans", column...
>
> --
>

Cannot delete maintenance plan.

Cannot delete a maintenance plan. SQL 2005 patched service pack 2. (That
means I was dumb enough to apply service pack 2 immediately and then
microsoft release a patch a couple days later because spk2 was broken)
The DELETE statement conflicted with the REFERENCE constraint
"FK_subplan_job_id". The conflict occurred in database "msdb", table
"dbo.sysmaintplan_subplans", column...

Apparently the job you try to delete has one or more subplans that (for some
reason) should be deleted apart.
Try deleting the single subplans before deleting the Maintenance Plan itself.
Each Subplan has foreign keys referencing the sysjobs table, containing the
definition of the Plan.
I cannot define the exact cause of your issue because i don't know the
complete history of the plan, his subplans, the schedules etc.
Look in msdb at the system tables to undertand the relationships between the
various table but DON'T TRY to operate updates directly over them!
Gilberto Zampatti
"SQLdba" wrote:

> Cannot delete a maintenance plan. SQL 2005 patched service pack 2. (That
> means I was dumb enough to apply service pack 2 immediately and then
> microsoft release a patch a couple days later because spk2 was broken)
> The DELETE statement conflicted with the REFERENCE constraint
> "FK_subplan_job_id". The conflict occurred in database "msdb", table
> "dbo.sysmaintplan_subplans", column...
>
> --
>

Cannot delete maintenance plan.

Cannot delete a maintenance plan. SQL 2005 patched service pack 2. (That
means I was dumb enough to apply service pack 2 immediately and then
microsoft release a patch a couple days later because spk2 was broken)
The DELETE statement conflicted with the REFERENCE constraint
"FK_subplan_job_id". The conflict occurred in database "msdb", table
"dbo.sysmaintplan_subplans", column...Apparently the job you try to delete has one or more subplans that (for some
reason) should be deleted apart.
Try deleting the single subplans before deleting the Maintenance Plan itself
.
Each Subplan has foreign keys referencing the sysjobs table, containing the
definition of the Plan.
I cannot define the exact cause of your issue because i don't know the
complete history of the plan, his subplans, the schedules etc.
Look in msdb at the system tables to undertand the relationships between the
various table but DON'T TRY to operate updates directly over them!
Gilberto Zampatti
"SQLdba" wrote:

> Cannot delete a maintenance plan. SQL 2005 patched service pack 2. (That
> means I was dumb enough to apply service pack 2 immediately and then
> microsoft release a patch a couple days later because spk2 was broken)
> The DELETE statement conflicted with the REFERENCE constraint
> "FK_subplan_job_id". The conflict occurred in database "msdb", table
> "dbo.sysmaintplan_subplans", column...
>
> --
>

Cannot delete Maintenance Plan job

Hi
I had a Maintenance Plan with a scheduled job setup in SQL 2005. I have
deleted the Plan without deleting the scheduled job. Now I cannot delete the
job without the Plan. Any help will be great appreciated.
I have deleted the job by setting up another maintenance plan. After the new
maintenance plan ran, the orphaned job was deleted. Sorry, I think I should
have posted this on another newsgroup. Thanks!
"Yuhong" wrote:

> Hi
> I had a Maintenance Plan with a scheduled job setup in SQL 2005. I have
> deleted the Plan without deleting the scheduled job. Now I cannot delete the
> job without the Plan. Any help will be great appreciated.