Monday, March 16, 2015

Upgrade to CRM 2013 - The INSERT statement conflicted with the FOREIGN KEY constraint "solution_base_dependencynode"

A few weeks ago, I (unfortunatelly) experimented a strange error while upgrading from CRM 2011 to CRM 2013.

When importing our CRM 2011 Organization into a new CRM 2013 Deployment, the import wizard was stopping with the following error:
"System.Exception: Error.ActionFailed Microsoft.Crm.Tools.Admin.PopulateDependencyNodesAction ---> System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "solution_base_dependencynode". The conflict occurred in database "tst_ru18_MSCRM", table "dbo.SolutionBase", column 'SolutionId'."

The CRM 2013 Deployment had Rollup 2 installed.
The CRM 2011 Organization database was at Rollup 18, but I also tried with Rollup 6 and 14, and I got the same error.
The CRM 2011 Organization came from an acceptance environment and contained a managed solution with all the customizations.
The weird thing was that the development organization had been upgraded without issue, but this one had all customizations in an unmanaged solution.

After some troubleshooting, I identified that the import process was trying to insert records into the DependencyNodeBase table, but as those records were referencing a solution that did not exist, the table constraint "solution_base_dependencynode" was breaking the INSERT statement.

Even activating the verbose logging on the CRM 2013 Deployment but did not give more information on what records cannot be inserted.

As I was stuck on the issue, I posted a description of the issue on the Microsoft Partner Network.
Actually, that description was the basis of this article.

A technical support engineer replied quite quickly and gave me the following SQL query to identify the corrupted information. (Special thanks to Hua Chen, original post is here http://partnersupport.microsoft.com/en-us/mpndynamics/forum/mpndyncrm/error-upgrading-from-crm-2011-to-crm-2013-on/18c684c6-aa90-4a9c-ac6f-8a3d4e1382f2)

SQL Query:
select a.SolutionId, f.SolutionId, f.OverwriteTime, f.ComponentState, f.IsManaged, f.* from FieldPermissionBase f inner join AttributeView a on a.LogicalName = f.AttributeLogicalName inner join EntityView e on e.ObjectTypeCode = f.EntityName where f.SolutionId not in (select SolutionId from Solution)

The SQL did return a few records corresponding to Field Security Profiles that was obviously corrupted.
To get my CRM 2011 organization imported, I had to go back in the CRM 2011 acceptance deployment and disable the "Field Security" boolean on the fields themselves (into the Default Solution).

Hope this will help someone that would have the same problem.

4 comments:

  1. Just ran into that exact issue with my upgrade, glad I found your post, thanks for sharing!

    ReplyDelete
  2. I have ran this SQL sentence but I did not get a record. What else could be the cause of this issue?

    ReplyDelete
  3. Great advice, helped me out. Thanks for sharing!

    ReplyDelete
  4. Ran this, got 38 affected rows in the DB - but don't know how to find the affected fields in CRM2011, and change the field security

    ReplyDelete