Relationship names change when importing customizations to a new Crm Install

Problem

After reading about this issue on a forum recently I decided to post my experience and workaround for the problem. The problem is that the schema names of relationships change when they are imported to a new Crm install (call it live) from an existing Crm (call it dev). The most common naming problem is that the prefix "new" is added to the relationship name. This causes several issues:

1. When making changes on dev and then trying to roll it out again you get the following error: "Failure: abc_entity1_abc_entity2: An attribute with the specified name already exists. Please specify a unique name." When you open up the relationship from the customizations section in Crm, you see that the above relationship is now called new_abc_entity1_abc_entity2.
2. When using code to retrieve records based on their ManyToMany relationships, as stated in the SDK, you need the linking entity name to be this name that has changed to new_abc_entity1_abc_entity2. If this link name changes then your code will also through an error saying that the relationship "abc_entity1_abc_entity2" does not exist.

It has to be said that this naming problem occurs across all the custom relationships imported. I have tracked down the problem to be caused by the order that things get imported into Crm. All entities and relationships get created before the "General system settings" get imported. I can only assume that at this point the new Crm still thinks that it's prefix should be "new" instead of "abc" in the above example.

Solution

I have found two ways to combat this, one proactive and one reactive. Please read through it carefully before doing and make sure you understand what happens and the risks involved.

The first way is to import all the customizations of type "Settings" first, and then import the whole file. The "Settings" nodes will be exported when all customizations are exported. So when importing customizations, simply order the grid by type and select all the settings entitie and click "Import Selected Customizations" (it may be worth while to then go into your Crm's system settings and make sure the prefix is now correct). Once this is done, to a full import of the same customization file. I have not tested this thoroughly yet, but the few occasions I tested it, it solved my problem.

The second one is if, after the first solution, there are still some wrong relationship names OR the first step was never implemented. For this you will have to manually change the relationship names in SQL. Please adapt each of the sql statements to reflect your system, and maybe even do it for each individual relationship one at a time to make sure you don't break anything.

DISCLAIMER: MAKE SURE YOUR DATABASE IS BACKED UP BEFORE DOING THIS AS THIS COULD BREAK YOUR CRM IF NOT DONE RIGHT. DON NOT RUN IT ON A LIVE SYSTEM UNLESS IT IS PROPERLY TESTED.

Inside SQL management studio, back up your sql database and run the following query to see if the correct values are displayed. If your naming convention was non-standard you may need to adapt some sql statements to suit:

select substring(SchemaName,1,4) as oldPrefix, substring(SchemaName,5,999) as noPrefix, 'abc_'+substring(SchemaName,5,999) as withNewPrefix from MetadataSchema.EntityRelationship where SchemaName like 'new_%'

If the resultset is as expected, run the following which will update the customization relationship names so that customizations can be imported, note that it may be worth testing it on one entity import first by adapting the where clause:

update MetadataSchema.EntityRelationship set SchemaName = 'abc_'+substring(SchemaName,5,999) where SchemaName like 'new_%'

Problem is now that the above breaks all the associated views, but this can be fixed by using the following sql statement, once again, please test it on one entity first because it could have adverse effects:

update MetadataSchema.Relationship set Name = 'abc_'+substring(Name,5,999) where Name like 'new_%'

Thanks and please post any questions or comments so that I can adapt this post accordingly.

Bossie