Sunday, March 4, 2012

Deletion State Code Attribute Leftovers in 2011 from Dynamics CRM 4

Once of the things I had not noticed thus far in CRM 2011 was that Microsoft had gotten rid of the deletion state code column.  If you're not familiar, it was a column CRM used to mark items as deleted and a service came along later to clean them up.  You may have run in to situations, particularly testing, where you insert an item with a fixed GUID, delete it, run again and it fails because the GUID is not unique.


An overview and history of this can be seen over at the Avanade blog.


I just starting hitting an issue with the removal of the deletion state code.  When instances are upgraded from CRM 4 to CRM 2011, the deletion state code columns on lookups (those attributes with the same name as your lookup, ending in dsc) was being included.


However, when you export your solution from CRM 2011 and import to a clean CRM 2011 instance, the DSC columns are not deployed.  So what we ended up with was our staging instance full of DSC columns and our production environment for a new customer who hadn't been using CRM 4 without DSC columns.


When we went to move a change to an entity in its own solution, a bunch of DSC columns were being exported as required dependencies in the solution.  When we went to import in the production instance, those required dependencies were not found and CRM would not let us continue with the import.


The import of the solution 'Your Solution' failed.  The following components are missing in your system and are not included in the solution.  Import the managed solutions that contain these components (Active) and then try importing this solution again.


Following was the clean-up necessary.  I highly recommend you back up the database before performing these actions.  I also recommend you run the select and understand everything you will be deleting!  These scripts were the answer to one particular scenario and I figure others will eventually have the same problem.



-- Delete the custom DSC attributes from the database.
DELETE a
--select A.LogicalName 
FROM   metadataschema.attribute a
       INNER JOIN metadataschema.entity e
         ON e.entityid = a.entityid
WHERE  a.attributeof IS NOT NULL
       AND a.attributetypeid = '00000000-0000-0000-00AA-110000000019'
       AND a.logicalname LIKE '%dsc'
       AND a.iscustomfield = 1

-- Delete the dsc attribute map columns
DELETE am
--select AM.* 
FROM   dependencybase db
       INNER JOIN dependencynodebase dnb
         ON db.dependentcomponentnodeid = dnb.dependencynodeid
       INNER JOIN attributemapbase am
         ON am.attributemapid = dnb.objectid
WHERE  db.requiredcomponentnodeid IN (SELECT dnb.dependencynodeid
                                      FROM   dependencynodebase dnb
                                             LEFT JOIN metadataschema.attribute
                                                       a
                                               ON dnb.objectid = a.attributeid
                                      WHERE  dnb.componenttype = 2
                                             AND a.logicalname IS NULL)

-- delete attribute map required dependencies on dsc columns
DELETE db
FROM   dependencybase db
       INNER JOIN dependencynodebase dnb
         ON db.dependentcomponentnodeid = dnb.dependencynodeid
WHERE  db.dependentcomponentnodeid IN (SELECT dnb.dependencynodeid
                                       FROM   dependencynodebase dnb
                                              LEFT JOIN metadataschema.attribute
                                                        a
                                                ON dnb.objectid = a.attributeid
                                       WHERE  dnb.componenttype = 2
                                              AND a.logicalname IS NULL)

-- Delete the attribute dependencies on dsc columns
DELETE db
FROM   dependencybase db
       INNER JOIN dependencynodebase dnb
         ON db.dependentcomponentnodeid = dnb.dependencynodeid
WHERE  db.requiredcomponentnodeid IN (SELECT dnb.dependencynodeid
                                      FROM   dependencynodebase dnb
                                             LEFT JOIN metadataschema.attribute
                                                       a
                                               ON dnb.objectid = a.attributeid
                                      WHERE  dnb.componenttype = 2
                                             AND a.logicalname IS NULL)

-- Delete the dependency nodes
DELETE dnb
-- select A.LogicalName 
FROM   dependencynodebase dnb
       LEFT JOIN metadataschema.attribute a
         ON dnb.objectid = a.attributeid
WHERE  dnb.componenttype = 2
       AND a.logicalname IS NULL 



After this recycle the asynchronous service and CRM application pool just to be safe.  Re-export your solution and things should be solid.