Posted in Deployment, Uncategorized

Singleton Retrieve Query Should Not Return More Than 1 Record [Manged Solution Import Error – OnPremise]

This is a common error during Managed solution deployments in Dynamics CRM Onpremise environments.

Cause:

This often caused by orphaned CustomControlDefaultConfig records. Its an internal CRM entity.  During back to back managed solution deployment, CRM will create these unwanted orphaned records.

Generally the solution import will take so much time and will not complete – you will have to look into event viewer or trace to look for this error:

singleton.JPG

Solution:

Take the database backup and run below sql script on the database – OrgnnizationName_MSCRM :

/* This script removes possible CustomControlDefaultConfig orphaned records and their associated dependency records.

Acronyms:
OTC = Object Type Code
CCDC = Custom Control Default Config
PETC = Primary Entity Type Code (OTC of entity associated with CCDC)

Steps:
1) Filter Records to correctRecords and recordsToDelete Local Tables
– Scenario 1: CCDC records associated with 2 ids or 2 PETCs
*Calculation 1: Single PrimaryEntityTypeCode records
*Calculation 2: Single CustomControlDefaultConfigId records
*Calculation 3: Multiple records with unique PrimaryEntityTypeCode and CustomControlDefaultConfigId
– Scenario 2: CCDC records associated with a deleted entity
*Includes records which have PETC > NextCustomObjectTypeCode (OTC for next custom entity)
2) Print Final Local Table Information:
– recordsToCheck
– Original table used to filter orphaned records
– Any records printed in this table were not sortable and will also appear in the recordsToDelete table
– recordsToDelete
– Orphaned (or unsortable) records to delete
– These records and all associated dependency records are deleted
– correctRecords
– Records which are correct in the DB
– associatedEntities
– Entities retrieved using PETC/OTC of deleted records
– These entities should be tested on the server to ensure proper behavior
3) Delete Orphaned Records from 3 DB Tables:
– CustomControlDefaultConfigBase
– DependencyBase
– DependencyNodeBase
4) Set Active Record for each PrimaryEntityTypeCode
– Only for single CCDC records associated with a PETC
– Mark as Active by setting OverwriteTime to the default value (1900-01-01 00:00:00.000)
*/

— Declare Variables
declare @recordsToCheck table (uniqueId uniqueidentifier, id uniqueidentifier, entityTypeCode int, overwriteTime DateTime, dependencyNodeId uniqueidentifier)
declare @recordsToCheckCopy table (uniqueId uniqueidentifier, id uniqueidentifier, entityTypeCode int, overwriteTime DateTime, dependencyNodeId uniqueidentifier)
declare @recordsToDelete table (uniqueId uniqueidentifier, id uniqueidentifier, entityTypeCode int, overwriteTime DateTime, dependencyNodeId uniqueidentifier)
declare @correctRecords table (uniqueId uniqueidentifier, id uniqueidentifier, entityTypeCode int, overwriteTime DateTime, dependencyNodeId uniqueidentifier)
declare @singleRecords table (uniqueId uniqueidentifier, id uniqueidentifier, entityTypeCode int, overwriteTime DateTime, dependencyNodeId uniqueidentifier)
declare @associatedEntities table (entityTypeCode int, entityName nvarchar(50))
declare @activeRowCount INT
declare @NewLineChar as char(2) = char(13) + char(10)

/* Step 1: Filter Records */
print ‘Step 1: Filter Records’

/* Scenario 1: Retrieve possible CCDC orphaned records which satisfy the following conditions:
1) Associated with custom entity (OTC > 10000)
2) Associated with an existing entity (entity has not been deleted)
3a) PETC associated with > 1 Id
— or —
3b) Id associated with > 1 PETC */
print ‘Scenario 1: Multiple Ids/PETCs’
insert into @recordsToCheck
select ccdc.CustomControlDefaultConfigIdUnique, ccdc.CustomControlDefaultConfigId, ccdc.PrimaryEntityTypeCode, ccdc.OverwriteTime, dn.DependencyNodeId
from CustomControlDefaultConfigBase ccdc
left join DependencyNodeBase dn
ON ccdc.CustomControlDefaultConfigId = dn.ObjectId
where (
CustomControlDefaultConfigId in (
select CustomControlDefaultConfigId
from CustomControlDefaultConfigBase
group by CustomControlDefaultConfigId
having count(distinct PrimaryEntityTypeCode) > 1
)
or PrimaryEntityTypeCode in (
select PrimaryEntityTypeCode
from CustomControlDefaultConfigBase
group by PrimaryEntityTypeCode
having count(distinct CustomControlDefaultConfigId) > 1
)
)
and PrimaryEntityTypeCode >= 10000
and PrimaryEntityTypeCode in (select ObjectTypeCode from Entity)
print ‘Inserted records into tableToCheck’

— Filter records depending on if they are correct and must be retained, or orphaned and must be deleted.
— Repeat logic process on records until there are no remaining records or no more can be sorted.
— There may be unsorted records left where it’s impossible to determine the correct records. These must also be deleted.
print @NewLineChar + ‘Begin Loop’
select @activeRowCount = Count(*) from @recordsToCheck
while (@activeRowCount <> 0)
BEGIN

— Make a copy of the updated recordsToCheck table
— This will be used to exit the while loop when all possible records have been sorted
delete from @recordsToCheckCopy
print ‘Deleted current recordsToCheckCopy’
insert into @recordsToCheckCopy
select * from @recordsToCheck
print ‘Updated recordsToCheckCopy’

/* Calculation 1: Single PrimaryEntityTypeCode Records */
print @NewLineChar + ‘Calculation 1: Single PrimaryEntityTypeCode Records’

— Find the records which only have 1 CCDC record associated with an existing entity (1 CCDC record per OTC/PETC)
— Insert into the singleRecords table
insert into @singleRecords
select * from @recordsToCheck
where entitytypecode in (
select entityTypeCode from @recordsToCheck
group by entityTypeCode
having count(entityTypeCode) = 1
)
print ‘Inserted records into singleRecords for Type Code’

— Using the singleRecords table, find records which have the same id associated (although they have different PETCs)
— We cannot decipher which is the correct records and must delete all records with the same id
insert into @recordsToDelete
select * from @singleRecords
where id in (
select id from @singleRecords
group by id
having count(id) > 1
)
print ‘Inserted records into recordsToDelete for all single records with same Id’

— Remove these incorrect records from recordsToCheck
delete rtc
from @recordsToCheck rtc
inner join @recordsToDelete rtd
on rtd.uniqueId = rtc.uniqueId
print ‘Removed same incorrect records from recordsToCheck’

— Remove these incorrect records from singleRecords
delete sr
from @singleRecords sr
inner join @recordsToDelete rtd
on rtd.uniqueId = sr.uniqueId
print ‘Removed same incorrect records from singleRecords’

— The remaining records in singleRecords must be correct since there is no other CCDC associated with the entity
— We have already filtered out records with the same id and different PETCs
insert into @correctRecords
select * from @singleRecords
print ‘Inserted remaining single records into correctRecords’

— Remove these correct records from the recordsToCheck
delete rtc
from @recordsToCheck rtc
inner join @correctRecords cr
on cr.uniqueId = rtc.uniqueId
print ‘Removed same correct records from recordsToCheck’

–Remove these correct records from singleRecords
delete from @singleRecords
print ‘Removed remaining records from singleRecords’

— Move incorrect records (using same ID associated with above OTCs) to the table for record deletion
— The correct type codes are already associated with this ID, thus records with the same ID but different OTC must be orphaned records
insert into @recordsToDelete
select * from @recordsToCheck
where id in (select id from @correctRecords)
print ‘Inserted records into recordsToDelete which share same Id’

— Remove these incorrect records from the recordsToCheck table
delete from @recordsToCheck
where id in (select id from @correctRecords)
print ‘Removed same incorrect records from recordsToCheck’

/* Calculation 2: Single CustomControlDefaultConfigId Records */
print @NewLineChar + ‘Calculation 2: Single CustomControlDefaultConfigId Records’

— Find the records which only have 1 CCDC record associated with a specific Id
— Insert into the singleRecords table
insert into @singleRecords
select * from @recordsToCheck
where id in (
select id from @recordsToCheck
group by id
having count(id) = 1
)
print ‘Inserted records into singleRecords for Id’

— Using the singleRecords table, find records which have the same entityTypeCode associated (although they have different Ids)
— We cannot decipher which is the correct records and must delete all records with the same entityTypeCode
insert into @recordsToDelete
select * from @singleRecords
where entityTypeCode in (
select entityTypeCode from @singleRecords
group by entityTypeCode
having count(entityTypeCode) > 1
)
print ‘Inserted records into recordsToDelete for all single records with same Type Code’

— Remove these incorrect records from recordsToCheck
delete rtc
from @recordsToCheck rtc
inner join @recordsToDelete rtd
on rtd.uniqueId = rtc.uniqueId
print ‘Removed same incorrect records from recordsToCheck’

— Remove these incorrect records from singleRecords
delete sr
from @singleRecords sr
inner join @recordsToDelete rtd
on rtd.uniqueId = sr.uniqueId
print ‘Removed same incorrect records from singleRecords’

— The remaining records in singleRecords must be correct since there is no other CCDC associated with the Id
— We have already filtered out records with the same entityTypeCode and different Ids
insert into @correctRecords
select * from @singleRecords
print ‘Inserted remaining single records into correctRecords’

— Remove these correct records from the recordsToCheck
delete rtc
from @recordsToCheck rtc
inner join @correctRecords cr
on cr.uniqueId = rtc.uniqueId
print ‘Removed same correct records from recordsToCheck’

–Remove these correct records from singleRecords
delete from @singleRecords
print ‘Removed remaining records from singleRecords’

— Move incorrect records (using same entityTypeCode associated with above Ids) to the table for record deletion
— The correct Ids are already associated with this entityTypeCode, thus records with the same entityTypeCode but different Ids must be orphaned records
insert into @recordsToDelete
select * from @recordsToCheck
where entityTypeCode in (select entityTypeCode from @correctRecords)
print ‘Inserted records into recordsToDelete which share same Type Code’

— Remove these incorrect records from the recordsToCheck table
delete from @recordsToCheck
where entityTypeCode in (select entityTypeCode from @correctRecords)
print ‘Removed same incorrect records from recordsToCheck’

/* Calculation 3: Multiple Records with unique TypeCode and Id */
print @NewLineChar + ‘Calculation 3: Multiple Records with unique TypeCode and Id’

— Add records which have unique and matching OTCs and Ids to the correctRecords table
— This case is for multiple records that share both the same id and PETC
insert into @correctRecords
select * from @recordsToCheck rtc
where id in (
select id from @recordsToCheck
group by id
having count(distinct entityTypeCode) = 1
)
and entityTypeCode in (
select entityTypeCode from @recordsToCheck
group by entityTypeCode
having count(distinct id) = 1
)
print ‘Inserted records into correctRecords for multiple records with unique Type Codes and Ids’

— Remove these correct records from recordsToCheck table
delete rtc
from @recordsToCheck rtc
inner join @correctRecords cr
on cr.uniqueId = rtc.uniqueId
print ‘Removed same records from recordsToCheck’

— Reset count variable to ensure we are still finding records which can be sorted
select @activeRowCount = count(*) from (
select uniqueId from @recordsToCheckCopy
except
select uniqueId from @recordsToCheck) as subquery

END
print @NewLineChar + ‘End Loop’

— Move remaining recordsToCheck to table for deletion. They may contain both orphaned and valid records,
— but it is impossible to decipher which are the orphaned records in the DB.
— Note: For valid records getting deleted, new CCDC records will get created for an entity if the control tab configuration is updated.
insert into @recordsToDelete
select * from @recordsToCheck
print ‘Inserted unsorted records into recordsToDelete’

/* Scenario 2: Retrieve CCDC orphaned records which are associated with an entity that no longer exists.
This will include records with PETC > NextCustomObjectTypeCode since they are leftover from deleted entities */
print @NewLineChar + ‘Scenario 2: No Associated Entity’
insert into @recordsToDelete
select ccdc.CustomControlDefaultConfigIdUnique, ccdc.CustomControlDefaultConfigId, ccdc.PrimaryEntityTypeCode, ccdc.OverwriteTime, dn.DependencyNodeId
from CustomControlDefaultConfigBase ccdc
left join DependencyNodeBase dn
on ccdc.CustomControlDefaultConfigId = dn.ObjectId
where ccdc.PrimaryEntityTypeCode not in(select ObjectTypeCode from Entity)
print ‘Inserted records into recordsToDelete with no associated entity’

/* Gather Entity Information for Deleted Records */
insert into @associatedEntities
select ObjectTypeCode, Name from Entity
where ObjectTypeCode in (
select entityTypeCode
from @recordsToDelete
)

/* Step 2: Print Local Table Information */
print @NewLineChar + ‘Step 2: Local Table Information’

— Print the records to delete to the console
— These are the orphaned records in the DB
declare @xmlRecordsToDelete xml = (select * from @recordsToDelete for XML auto)
print convert(nvarchar(max), @xmlRecordsToDelete)

— Print the entity information associated with the deleted records using OTC/PETC
— These are the entities that should be tested on the server
declare @xmlAssociatedEntities xml = (select * from @associatedEntities for XML auto)
print convert(nvarchar(max), @xmlAssociatedEntities)

— Print any remaining unsorted records. These will also appear in the recordsToDelete table
— If there exist unsorted records, the solution used for import may also be in a bad state
declare @xmlRecordsToCheck xml = (select * from @recordsToCheck for XML auto)
print convert(nvarchar(max), @xmlRecordsToCheck)

— Print correct records which are kept in the DB
declare @xmlCorrectRecords xml = (select * from @correctRecords for XML auto)
print convert(nvarchar(max), @xmlCorrectRecords)

/* Step 3: Delete Orphaned Records */
print @NewLineChar + ‘Step 3: Delete Orphaned Records’

— Delete records from the CCDC table
delete from CustomControlDefaultConfigBase
where CustomControlDefaultConfigIdUnique in (select uniqueId from @recordsToDelete)
print ‘Deleted from CustomControlDefaultConfigBase’

— Delete associated dependency records
— Only delete if not associated with any correct records
delete from DependencyBase
where DependentComponentNodeId in (
select rtd.dependencyNodeId from @recordsToDelete rtd
inner join @correctRecords cr
on rtd.dependencyNodeId <> cr.dependencyNodeId
)
or RequiredComponentNodeId in (
select rtd.dependencyNodeId from @recordsToDelete rtd
inner join @correctRecords cr
on rtd.dependencyNodeId <> cr.dependencyNodeId
)
print ‘Deleted from DependencyBase’

— Delete associated dependency node records
— Only delete if not associated with any correct records
delete from DependencyNodeBase
where ObjectId in (
select rtd.id from @recordsToDelete rtd
inner join @correctRecords cr
on rtd.id <> cr.id
)
print ‘Deleted from DependencyNodeBase’

/* Step 4: Set Active Record for each PrimaryEntityTypeCode */
print @NewLineChar + ‘Step 4: Set Active Record for each PrimaryEntityTypeCode’

— For each correct record whose PETC is only associated with this record, mark it as the active record.
— Since it is the only record associated with the entity whose OTC = PETC, it must be the active record.
— Setting overwrite time to the default value will mark the record as active.
update CustomControlDefaultConfigBase
set OverwriteTime = ‘1900-01-01 00:00:00.000’
where CustomControlDefaultConfigIdUnique in (
select uniqueId from @correctRecords where entitytypecode in (
select entityTypeCode from @correctRecords
group by entityTypeCode
having count(entityTypeCode) = 1
)
)

 

I hope this helps.

Cheers!