CRM 4.0 Upgrade error. An item with the same key has already been added

Recently when testing a CRM 4.0 upgrade for a customer, I got the error 'An item with the same key has already been added'. I'm not the first to have met this problem (see the MS newsgroup), and probably won't be the last).

From what I've found so far there are 2 possible causes, both to do with CRM believing there are duplicate field names. Either a duplication has occured between a custom field, and a virtual field, or you have somehow got a duplicate entry in FieldXml.

Duplication between a custom field, and a virtual field
For certain field types (boolean, picklist and lookup), CRM creates an additional attribute in the metadata for the text of the field. This attrbiute is not physically stored, and is considered to be a 'virtual' field. The virtaul attribute has the name of the base field + a suffix of 'name'. For example, the prioritycode picklist field results in a corresponding prioritycodename virtual field, and a custom lookup field new_accountid would result in a virtual field new_accountidname (there is also a new_accountiddsc virtual).

This can cause a problem if you created an additional, physical field with the same name as a virtual field. CRM will let you do this, but the upgrade could fail.

To determine if this is a potential problem, run the following SQL script in the METABASE SQL database. This will return a list of any duplicates (you'll need dbo rights to run the script)

select e.name as entity, a.name as attribute, count(*)
from attribute a join entity e on a.entityid = e.entityid
group by e.name, a.name
having count(*) > 1

If you've found duplicates, your only solution is to remove the duplicate field from CRM.

Duplicate entry in FieldXml
CRM stores metadata in several places in CRM. One of them is the OrganizationUIBase table in the MSCRM database. Within this table, some data is stored in the FieldXml field. This is an XML document that looks something like this:

<entity name="prism_utilisationperiod" objecttypecode="10003">
<fields>
<field name="prism_utilisationperiodid" requiredlevel="na">
<displaynames>
<displayname description="UtilisationPeriod" languagecode="1033" />
</displaynames>
</field>
<field name="createdon" requiredlevel="na" format="date">
<displaynames>
<displayname description="Created On" languagecode="1033" />
</displaynames>
</field>
</fields>
</entity>

There should only be one field element for each CRM attribute, but the problem I had was that a field element was duplicated.

To determine any duplicate fields, use the following SQL in the MSCRM database:

declare @fieldXml nvarchar(max), @otc int, @idoc int
declare curEntity cursor fast_forward for
select ObjectTypeCode, FieldXml from OrganizationUIBase where InProduction = 1
open curEntity
fetch next from curEntity into @otc, @fieldXml
while @@fetch_status = 0
begin
EXEC sp_xml_preparedocument @idoc OUTPUT, @fieldXml
if exists ( select name as field from OpenXML(@idoc, '/entity/fields/field') WITH (name nvarchar(64)) group by name having count(*) > 1 )
select @otc as otc, name as field, count(*) as occurences
from OpenXML(@idoc, '/entity/fields/field') WITH (name nvarchar(64))
group by name
having count(*) > 1
EXEC sp_xml_removedocument @idoc
fetch next from curEntity into @otc, @fieldXml
end
close curEntity
deallocate curEntity

This will list the objecttypecode and attribute name of any duplicates. If you have duplicates, the resolution is to export the customisations for the relevant entities, delete any duplicate field elements from the customisation xml, then reimport the xml and publish the entities.

Credit is due to Michael Hohne and Kesh Patel who've contributed to finding the problems and solutions.