Bizarre Error - insert into dbo.sysmaintplan_subplans fails?
I have a maintenance plan that consists of several parts. It basically backs up all the databases, deletes old backups and then shrinks the databases. The odd thing is that it appears to back up all the databases, can't tell if it does step 2 or 3 and then it fails with the errors below. How do i correct this short of throwing it out and starting from scratch? This is a package originally from one server that i'm trying to deploy to a 2nd server. Already using configuration files to chagne the target connections etc. thx. PackageStart,WSWT4361,NT AUTHORITY\SYSTEM,ProdBackupPlan,{645B67A9-0377-4462-BE81-755D4B1CE9DD},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:16 PM,2/28/2006 5:09:16 PM,0,0x,Beginning of package execution. OnError,WSWT4361,NT AUTHORITY\SYSTEM,{56B53144-6DA7-4276-B37B-A09B1254DD3C},{56B53144-6DA7-4276-B37B-A09B1254DD3C},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:17 PM,2/28/2006 5:09:17 PM,-1073548784,0x,Executing the query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp_maintplan_open_logentry '{645B67A9-0377-4462-BE81-755D4B1CE9DD}', '{92E2538E-BED5-4935-897A-AB6ACAEC373A}',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. OnError,WSWT4361,NT AUTHORITY\SYSTEM,OnPreExecute,{B88BD5B5-138F-4024-A2A5-D60403296701},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:17 PM,2/28/2006 5:09:17 PM,-1073548784,0x,Executing the query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp_maintplan_open_logentry '{645B67A9-0377-4462-BE81-755D4B1CE9DD}', '{92E2538E-BED5-4935-897A-AB6ACAEC373A}',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. OnError,WSWT4361,NT AUTHORITY\SYSTEM,ProdBackupPlan,{645B67A9-0377-4462-BE81-755D4B1CE9DD},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:17 PM,2/28/2006 5:09:17 PM,-1073548784,0x,Executing the query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp_maintplan_open_logentry '{645B67A9-0377-4462-BE81-755D4B1CE9DD}', '{92E2538E-BED5-4935-897A-AB6ACAEC373A}',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.
March 1st, 2006 4:15am

I've found through trial and error that implicit or explicit conversion from uniqueidentifier to string returns all uppercase letters while the inverse conversion from string to uniqueidentifier FAILS if you pass the same string in orignal uppercase letters. I've been using lower() function anytime I want to convert string to uniqueidentifier. It's quiet annoying if you ask me why this was implemented as such. following explicit conversion addresses the case issue I've mentioned. Cast(lower(myguidstring) as uniqueidentifier)
Free Windows Admin Tool Kit Click here and download it now
November 8th, 2006 11:08pm

I have been attempting for the same. The key was finding a way to execute the package in the server context through SQL Agent job - See my full blog here to see how to create a generic SSIS package. http://archpulse.wordpress.com/2011/06/14/a-portable-generic-maintenance-plan The failure is due to a missing entry in dbo.sysmaintplan_subplans...
June 14th, 2011 4:26pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics