Hi,

I'm running a differential backup every night using a maintenance plan. The
backup is successfully created (I have restored from one of these backups)
but the job always report failure - see below:

Hope someone can

Thanks
Adam



Date 19/05/2008 10:41:21
Log Job History (User Tables.Differential Backup)

Step ID 1
Server SQLDB3\DB3
Job Name User Tables.Differential Backup
Step Name Differential Backup
Duration 00:00:12
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: COMPUTER_SERV\sqlcluster. ...042.00 for 32-bit Copyright
(C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:41:21
Error: 2008-05-19 10:41:21.98 Code: 0xC002F210 Source:
{41AC03F1-47A0-4AFF-A06C-D6B946F14ADF} Execute SQL Task Description:
Executing the query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE
msdb..sp_maintplan_open_logentry '{2C3DD0AF-5F43-41A6-94CC-102B0E442C41}',
'{657040F3-E1C1-491C-B7D0-0D08BDB8905D}',NULL, @Guid OUTPUT Select
CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The
INSERT statement conflicted with the FOREIGN KEY constraint
"FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb",
table "dbo.sysmaintplan_subplans", column 'subplan_id'. The statement has
been terminated.". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or
connection not established correctly. End E... The package execution fa...
The step failed.

There have been a lot of problems with bugs in the maint plans that were
fixed in SP2 and subsequent CU's. What version are you running? Also you
may want to try deleting that plan and recreating from scratch or simply
creating a scheduled job outside the maint plan to do the diff backup.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Adam Sankey" <AdamSankey@discussions.microsoft.com> wrote in message
newsFBD5F98-2450-41A3-AB31-AAADEA209FEC@microsoft.com...
> Hi,
>
> I'm running a differential backup every night using a maintenance plan.
> The
> backup is successfully created (I have restored from one of these backups)
> but the job always report failure - see below:
>
> Hope someone can
>
> Thanks
> Adam
>
>
>
> Date 19/05/2008 10:41:21
> Log Job History (User Tables.Differential Backup)
>
> Step ID 1
> Server SQLDB3\DB3
> Job Name User Tables.Differential Backup
> Step Name Differential Backup
> Duration 00:00:12
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
>
> Message
> Executed as user: COMPUTER_SERV\sqlcluster. ...042.00 for 32-bit
> Copyright
> (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:41:21
> Error: 2008-05-19 10:41:21.98 Code: 0xC002F210 Source:
> {41AC03F1-47A0-4AFF-A06C-D6B946F14ADF} Execute SQL Task Description:
> Executing the query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE
> msdb..sp_maintplan_open_logentry '{2C3DD0AF-5F43-41A6-94CC-102B0E442C41}',
> '{657040F3-E1C1-491C-B7D0-0D08BDB8905D}',NULL, @Guid OUTPUT Select
> CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error:
> "The
> INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_sysmaintplan_log_subplan_id". The conflict occurred in database
> "msdb",
> table "dbo.sysmaintplan_subplans", column 'subplan_id'. The statement has
> been terminated.". Possible failure reasons: Problems with the query,
> "ResultSet" property not set correctly, parameters not set correctly, or
> connection not established correctly. End E... The package execution
> fa...
> The step failed.
>

I'm currently on the following version:

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: Service Pack 2)

Is there any CU's I should apply?

Thanks
Adam

"Andrew J. Kelly" wrote:

> There have been a lot of problems with bugs in the maint plans that were
> fixed in SP2 and subsequent CU's. What version are you running? Also you
> may want to try deleting that plan and recreating from scratch or simply
> creating a scheduled job outside the maint plan to do the diff backup.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "Adam Sankey" <AdamSankey@discussions.microsoft.com> wrote in message
> newsFBD5F98-2450-41A3-AB31-AAADEA209FEC@microsoft.com...
> > Hi,
> >
> > I'm running a differential backup every night using a maintenance plan.
> > The
> > backup is successfully created (I have restored from one of these backups)
> > but the job always report failure - see below:
> >
> > Hope someone can
> >
> > Thanks
> > Adam
> >
> >
> >
> > Date 19/05/2008 10:41:21
> > Log Job History (User Tables.Differential Backup)
> >
> > Step ID 1
> > Server SQLDB3\DB3
> > Job Name User Tables.Differential Backup
> > Step Name Differential Backup
> > Duration 00:00:12
> > Sql Severity 0
> > Sql Message ID 0
> > Operator Emailed
> > Operator Net sent
> > Operator Paged
> > Retries Attempted 0
> >
> > Message
> > Executed as user: COMPUTER_SERV\sqlcluster. ...042.00 for 32-bit
> > Copyright
> > (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:41:21
> > Error: 2008-05-19 10:41:21.98 Code: 0xC002F210 Source:
> > {41AC03F1-47A0-4AFF-A06C-D6B946F14ADF} Execute SQL Task Description:
> > Executing the query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE
> > msdb..sp_maintplan_open_logentry '{2C3DD0AF-5F43-41A6-94CC-102B0E442C41}',
> > '{657040F3-E1C1-491C-B7D0-0D08BDB8905D}',NULL, @Guid OUTPUT Select
> > CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error:
> > "The
> > INSERT statement conflicted with the FOREIGN KEY constraint
> > "FK_sysmaintplan_log_subplan_id". The conflict occurred in database
> > "msdb",
> > table "dbo.sysmaintplan_subplans", column 'subplan_id'. The statement has
> > been terminated.". Possible failure reasons: Problems with the query,
> > "ResultSet" property not set correctly, parameters not set correctly, or
> > connection not established correctly. End E... The package execution
> > fa...
> > The step failed.
> >
>
>

If memory serves me that is SP2 and there were issues with the MP's in the
raw Sp2 and some of the immediate hotfixes. CU 7 is the latest CU and fixes
a lot of bugs. I can't say if it will fix that one though. As always test
first.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Adam Sankey" <AdamSankey@discussions.microsoft.com> wrote in message
news:A62AFE44-3651-4575-9251-E3C196023439@microsoft.com...
> I'm currently on the following version:
>
> Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007
> 22:47:07
> Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on
> Windows
> NT 5.2 (Build 3790: Service Pack 2)
>
> Is there any CU's I should apply?
>
> Thanks
> Adam
>
> "Andrew J. Kelly" wrote:
>
>> There have been a lot of problems with bugs in the maint plans that were
>> fixed in SP2 and subsequent CU's. What version are you running? Also you
>> may want to try deleting that plan and recreating from scratch or simply
>> creating a scheduled job outside the maint plan to do the diff backup.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>>
>> "Adam Sankey" <AdamSankey@discussions.microsoft.com> wrote in message
>> newsFBD5F98-2450-41A3-AB31-AAADEA209FEC@microsoft.com...
>> > Hi,
>> >
>> > I'm running a differential backup every night using a maintenance plan.
>> > The
>> > backup is successfully created (I have restored from one of these
>> > backups)
>> > but the job always report failure - see below:
>> >
>> > Hope someone can
>> >
>> > Thanks
>> > Adam
>> >
>> >
>> >
>> > Date 19/05/2008 10:41:21
>> > Log Job History (User Tables.Differential Backup)
>> >
>> > Step ID 1
>> > Server SQLDB3\DB3
>> > Job Name User Tables.Differential Backup
>> > Step Name Differential Backup
>> > Duration 00:00:12
>> > Sql Severity 0
>> > Sql Message ID 0
>> > Operator Emailed
>> > Operator Net sent
>> > Operator Paged
>> > Retries Attempted 0
>> >
>> > Message
>> > Executed as user: COMPUTER_SERV\sqlcluster. ...042.00 for 32-bit
>> > Copyright
>> > (C) Microsoft Corp 1984-2005. All rights reserved. Started:
>> > 10:41:21
>> > Error: 2008-05-19 10:41:21.98 Code: 0xC002F210 Source:
>> > {41AC03F1-47A0-4AFF-A06C-D6B946F14ADF} Execute SQL Task
>> > Description:
>> > Executing the query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE
>> > msdb..sp_maintplan_open_logentry
>> > '{2C3DD0AF-5F43-41A6-94CC-102B0E442C41}',
>> > '{657040F3-E1C1-491C-B7D0-0D08BDB8905D}',NULL, @Guid OUTPUT Select
>> > CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error:
>> > "The
>> > INSERT statement conflicted with the FOREIGN KEY constraint
>> > "FK_sysmaintplan_log_subplan_id". The conflict occurred in database
>> > "msdb",
>> > table "dbo.sysmaintplan_subplans", column 'subplan_id'. The statement
>> > has
>> > been terminated.". Possible failure reasons: Problems with the query,
>> > "ResultSet" property not set correctly, parameters not set correctly,
>> > or
>> > connection not established correctly. End E... The package execution
>> > fa...
>> > The step failed.
>> >
>>
>>

This is most likely due to a miss-match in your .dtsx files settings and the id's of your subplans stored in the msdb.

If you look in the .dtsx and search for your subplan schedules, you should see something like this for each schedule...


&lt;ddsxmlobj&gt;
&ltroperty name="Caption" value="Nightly Tasks" vartype="8" /&gt;
&ltroperty name="CaptionHeight" value="1164" vartype="3" /&gt;
&ltroperty name="Collapsed" value="0" vartype="11" /&gt;
&ltroperty name="CollapsedWidth" value="3998" vartype="3" /&gt;
&ltroperty name="LogicalObject" value="{60EB337D-9194-4232-835E-CADA732FF5AE}" vartype="8" /&gt;
&ltroperty name="RealHeight" value="0" vartype="3" /&gt;
&ltroperty name="RealWidth" value="0" vartype="3" /&gt;
&ltroperty name="ShowConnectorSource" value="0" vartype="2" /&gt;
&lt;/ddsxmlobj&gt;


The key here is it is storing the subplan_id in the LogicalObject.

If this value does not match the subplan's subplan_id, it won't run the job successfully and the maintenance plan won't show the subplan schedule when it is opened in SSMS.

To fix, you need to look in your dtsx and correct the values there or correct the msdb..sysmaintplan_subplans value for subplan_id