I'm using SQL Server 2008R and I experince the following error on every package I create and execute:

"Executing the query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp..." 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. "

Do you have any experience?

Below, the complete error messege

Executed as user: *******. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4000.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  6:37:47 AM  Error: 2014-02-01 06:37:47.81     Code: 0xC002F210     Source: {A77A0D78-E4F8-48DA-9933-68E53270789C} Execute SQL Task     Description: Executing the query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp..." 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 Error  Warning: 2014-02-01 06:37:47.81     Code: 0x80019002     Source: OnPreExecute      Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.  End Warning  Progress: 2014-02-01 06:37:48.37     Source: Check Database Integrity Task      Executing query "USE [*******]  ".: 50% complete  End Progress  Progress: 2014-02-01 06:37:51.58     Source: Check Database Integrity Task      Executing query "DBCC CHECKDB(N'*******')  WITH NO_INFOMSGS...".: 100% complete  End Progress  Progress: 2014-02-01 06:37:51.58     Source: Check Database Integrity Task      Executing query "USE [*******]  ".: 50% complete  End Progress  Progress: 2014-02-01 06:37:54.19     Source: Check Database Integrity Task      Executing query "DBCC CHECKDB(N'*******')  WITH NO_INFOMSG...".: 100% complete  End Progress  Error: 2014-02-01 06:37:54.20     Code: 0xC0024104     Source: Check Database Integrity Task      Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter.  End Error  Error: 2014-02-01 06:37:54.20     Code: 0xC0024104     Source: {23A7637D-F19B-4BDA-BF4E-D82C1A7F3682}      Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter.  End Error  Warning: 2014-02-01 06:37:54.20     Code: 0x80019002     Source: OnPostExecute      Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.  End Warning  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  6:37:47 AM  Finished: 6:37:54 AM  Elapsed:  6.692 seconds.  The package execution failed.  The step failed.

Thank you in advance for the help.

Martino Pavone

• Edited by Martino Pavone Monday, February 03, 2014 11:20 AM

May be the below links would help you:

http://stackoverflow.com/questions/6386665/ssis-package-within-msdb-maintenance-throwing-error-the-insert-statement-confl

http://mysqlperception.blogspot.in/2009/09/maintenance-plan-ssis-packages-cause.html

• Proposed as answer by Tracy CaiMicrosoft contingent staff, Moderator 6 hours 27 minutes ago

Hi,

The cause of this problem was that the job had become orphaned. We can prove this by doing the following test.

use msdb

go

select * from dbo.sysjobs

go

sysjobs in MSDB which stores the information for each scheduled job to be executed by SQL Server Agent.

Copy the job ID for the job that had stopped working and check in sysmaintplan_subplans for the same job ID.

use msdb

go

select * from sysmaintplan_subplans where job_id = <JOBID of that job that is a part of the maintenance plan which has stopped execution>

go

sysmaintplan_subplans in MSDB is updated by the SQL Server which contains the information like the job id ,Plan_id ,Sub_plan name, subplan_id.

If the job ID is present in the sysjobs but not in the sysmaintplan_subplans table then the job becomes orphaned.

You may get an output which returns no rows and returns only the column names. Or get the below error:

----------------------------

Msg 8169, Level 16, State 2, Line 1

Conversion failed when converting from a character string to uniqueidentifier.

-----------------------------

To work around this problem, manually create the record of the maintenance plan that you import. See the below KB article for details:

BUG: Error message when you try to save a maintenance plan in SQL Server Management Studio: "Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)"

http://support.microsoft.com/kb/922651

Thanks.

I have solved myself the problem, it was nothing directly related to the Maintenance Plan.
The problem was due SQL Server Browser, it must be up and running.
I'm going to explain...

I was experiencing a strange situation where I was able to access the SQL Server cluster instance using the cluster name but not using the named instance

<CLUSTERNAME>  -> I was able to logon
<CLUSTERNAME>\<NAMED INSTANCE> -> I was not able to logon

but in the past I remember that I was able to logon on into the instance in both ways.

Initially, I solved the maintenance plan problem just updating the configuration file "%ProgramFiles%\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml" replacing:
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.\NAMED INSTNACE</ServerName>
    </Folder>
with
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>CLUSTER NAME</ServerName>
    </Folder>
The problem with the Maintenance Plan was solved, but.......

I experienced a new problem delivering notification emails (from the Maintenance Plan).
Started again my investigations and I found out that all the email was in the Queue.
Looking in the logs I found out that SQL Server was not able to start a session because it was not able to connect to <CLUSTERNAME>\<NAMED INSTANCE>
Then I realized that this was the root cause of all the strange behaviors.

I made additional tests again:

<CLUSTERNAME>        ->   OK
<CLUSTERNAME>\<NAMED INSTANCE> -> FAILED
<CLUSTERNAME>\<NAMED INSTANCE>,1433 -> OK

After the above tests, all was clear. It is the SQL Server Browser that gives the port number to the client:
The client sends a UDP packet to SQL Browser on the target machine and the service return back the port number. The first connection (using <CLUSTERNAME>) works fine because the client, if SQL Server doesn't respond, tries the default port. Probably, on a named instance the client assumes that the port is necessarily different so the connection fails, but it works if you specify explicitly the port.

The sql server browser was disabled due to a security review in order to limit the attack surface. I didn't experienced any problem on the application because I use to create an alias using cliconf with a static port.

I hope that my experience may be helpful

Martino Pavone

 

 

 

 

 

 

• Marked as answer by Tracy CaiMicrosoft contingent staff, Moderator 4 hours 40 minutes ago

Hi,

Glad to hear the issue was resolved. Thank you for coming back and let us know the solution. It will definitely help other community members facing similar problems.