CRM 2011 - Ghost Dependencies

Long time since the last post sorry, but figured I would post about something odd I found in our CRM 2011 environment here.

When we were trying to delete an entity we were getting a message that we couldn't delete the entity due to dependencies showing a blank list!

After a lot of searching we found the error to be related to some orphaned records in the ReportEntityBase table that were set as a managed solution component.

I first attempted to remove this the supported way to no avail so had to come up with a 'hack' to get them out of the database.  I have no idea how you would do this with Cloud CRM.

First step was to backup the data about to be removed, just in case we had some downstream impact.

--Back the Dependencies, nodes and offending records

SELECT db.* INTO DependencyBase_backup FROM DependencyBase db 

JOIN DependencyNodeBase dnb ON dnb.DependencyNodeId = db.DependentComponentNodeId

JOIN ReportEntity rpe ON rpe.ReportEntityId = dnb.ObjectId

WHERE ComponentType = 32 and rpe.ObjectTypeCode = 10001

SELECT dnb.* INTO DependencyNodeBase_backup FROM DependencyNodeBase dnb

JOIN ReportEntity rpe ON dnb.ObjectId = rpe.ReportEntityId

WHERE ComponentType = 32 and rpe.ObjectTypeCode = 10001 

SELECT * INTO ReportEntityBase_backup FROM ReportEntityBase WHERE ObjectTypeCode = 10001

Next delete out the dependency records pointing to the problem records

 

DELETE db FROM DependencyBase db 

JOIN DependencyNodeBase dnb ON dnb.DependencyNodeId = db.DependentComponentNodeId

JOIN ReportEntity rpe ON rpe.ReportEntityId = dnb.ObjectId

WHERE ComponentType = 32 and rpe.ObjectTypeCode = 10001

Now delete the joining node records

 

DELETE dnb FROM DependencyNodeBase dnb

JOIN ReportEntity rpe ON dnb.ObjectId = rpe.ReportEntityId

WHERE ComponentType = 32 and rpe.ObjectTypeCode = 10001 

Delete the Orphaned records

DELETE FROM ReportEntityBase WHERE ObjectTypeCode = 10001