Managed Solution Field Name Conflict

EDIT: I have only tested this in a couple of different UR levels in CRM 2011.  Other than that, you may need to change things around to get it to work.

One issue I run into with managed solutions on a regular basis is field name conflicts.  This has been fairly well documented online, but the basis is that if you export a solution out of environment A as managed, import into environment B, then go back into A, delete the field, say to changed the data type, then attempt to import the managed solution back into B, you will get errors.  You need to then create a holding solution to get rid of the field out of B, which is a pain.  I have a couple clients that maybe don’t have the best processes in place when moving dev to prod, and frequently ran into issues with this, so what a colleague of had been doing is telling them to do was ALWAYS do the holding solution with every roll.  Since the solution was fairly complex this ended up eating up a bunch of time with every release.

Needless to say, while this resolved the issues, they weren’t real happy since 75%+ of the time there was no need to jump through the holding solution hoops.  However, just importing and seeing if it failed was also time consuming and their sys admins complained about having to “babysit” the app to see if it failed.  I decided to try to find a way to query across the two databases to see if I could determine in advance whether a holding solution was necessary quickly.  As it turns out, yes you can, but it requires setting up SQL to enable Ad Hoc Distributed Queries.  The below example is when moving from a dev to a test environment, both with their own SQL servers.

First off run the following, this turns on AHDQ:

sp_configure ‘show advanced options’, 1;

RECONFIGURE;

GO

sp_configure ‘Ad Hoc Distributed Queries’, 1;

RECONFIGURE;

GO

You can then run the below query.   If it returns any rows, you have a conflict of some sort that will need a holding solution to clean up.  If the values returned from the dev environment are NULL, that means you deleted the field out of dev, and the holding solution will be needed to clean up the field in test.  If all columns are returned, the field exists in both environments, but you have changed the datatype in dev.  Replace the items inside <<double angle brackets>>.

select tabA.AttributeId as ‘Att ID 1’,

m.AttributeId as ‘Att ID 2’,

tabA.AttributeTypeId as ‘Type ID 1’,

m.attributetypeid as ‘Type ID 2’,

tabA.Name as ‘Name 1’,

m.Name as ‘Name 2’,

tabA.AttributeRowId as ‘Row Id 1’,

m.AttributeRowId as ‘Row Id 2’,

tabA.EntName as ‘Entity Name’

from openrowset(‘SQLNCLI’, ‘Server=<<TEST_SQL>>;Trusted_Connection=yes;’,

                              ‘SELECT distinct ta.AttributeId, ta.AttributeTypeId, ta.Name, ta.AttributeRowId, te.Name as EntName FROM [<<TEST_ORG_DB>>].[MetadataSchema].[Attribute] ta join [<<TEST_ORG_DB>>].[MetadataSchema].[Entity] te

                              on ta.EntityId = te.EntityId’) as tabA

LEFT OUTER JOIN [<<DEV_ORG_DB>>].[MetadataSchema].[Attribute] m

on (tabA.Name = m.Name <<collate Latin1_General_CI_AI you may or may not need to do this>> and tabA.AttributeTypeId = m.AttributeTypeId)

where m.AttributeId is null

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: