SQL Generic error in Accounts Merge and Contacts Merge in Dynamics CRM 2011

Exception Message: SQL Generic error

Route Cause: Sometimes we used to get ‘SQL Generic error’ exceptions during Accounts merge or Contacts merge.  We enabled SQL Server Profiler and tried again merging for some of the failed merge accounts and contacts, traced what exception is, it is (Violation of UNIQUE KEY constraint ‘UQ_PrincipalObjectAccess’. Cannot insert duplicate key in object ‘dbo.PrincipalObjectAccess’)

For Accounts Merge: When any of Subordinate account contacts/sub accounts are shared with Master account owner, it creates a record in PrincipalObjectAccess table. When merging accounts, it is again trying to create the same record in PrincipalObjectAccess table,  since this record already exists in PrincipalObjectAccess table which causes Generic SQL error.

For Contacts Merge: It is same for contacts merge too, when Subordinate contact is already shared with Master contact owner, it creates a record in PrincipalObjectAccess table when merging contacts, it is again trying to create the same record in POA table, since this record already exists in PrincipalObjectAccess table which causes Generic SQL Error.

Solution 1: I tried to revoke those privilege records from PrincipalObjectAccess table using RevokeAccessRequest class in SDK, for some reason it is not deleting those records. It just sets AccessRightsMask and InheritedAccessRightsMark values to 0.  I guess this is what RevokeAccessRequest does, it just zero’s out the permissions shared rather than deleting the record.
Tried again merging accounts and contacts after revoking privilege records from PrincipalObjectAccess, for some reason it did not work.

Solution 2: Following solution worked for me, you can use following code do the same.

1. Try changing the master account owner to CRM Admin user
2. Merge the accounts/contacts
3. Change owner back to original owner who owns before Merge.

    private void MergeFailedAccounts(CRM.SDK.Account subOrdinateAccount, CRM.SDK.Account masterAccount, CRM.SDK.Account updateContent)
        {
            IOrganizationService service = CRMHelper.GetCRMConnection();

            OrgServiceWrapper orgServiceWrapper = (OrgServiceWrapper)service;
            //Get Organization Service Context User Credentials
            string strUserDomainName = orgServiceWrapper.ClientCredentials.Windows.ClientCredential.Domain + "\\" + orgServiceWrapper.ClientCredentials.Windows.ClientCredential.UserName;
            string userFetchXML = string.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true'>
                                                    <entity name='systemuser'>
                                                        <attribute name='systemuserid'/>
                                                        <filter type='and'><condition attribute='domainname' operator='eq' value='{0}' /></filter>
                                                    </entity>
                                                </fetch>", strUserDomainName);    
            //------------------
            //1-Get CRMS Service(Admin User) UserId for changing account owner
            //------------------
            var userFetchExp = new FetchExpression(userFetchXML);
            EntityCollection userEntityCollection = service.RetrieveMultiple(userFetchExp);
            if (userEntityCollection.Entities.Count > 0)
            {
                //------------------
                //2- Change Master Account owner to CRMS Service(Admin User)
                //------------------
                AssignRequest cRMSServiceUserAssignReq = new AssignRequest
                {
                    Assignee = new EntityReference("systemuser", userEntityCollection.Entities[0].Id),
                    Target = new EntityReference("account", masterAccount.Id)
                };
                // Execute the AssignRequest
                service.Execute(cRMSServiceUserAssignReq);

                //------------------
                //3-Merge Accounts
                //------------------
                MergeRequest request = new MergeRequest
                {
                    Target = new EntityReference("account", masterAccount.Id),
                    SubordinateId = subOrdinateAccount.Id,
                    UpdateContent = updateContent,
                };

                MergeResponse response = (MergeResponse)service.Execute(request);

                //------------------
                //4- Change Master Account Owner back to orginal owner who was before Merge
                //------------------
                AssignRequest accountOwnerAssignReq = new AssignRequest
                {
                    Assignee = masterAccount.OwnerId,
                    Target = new EntityReference("account", masterAccount.Id)
                };
                // Execute the AssignRequest
                service.Execute(accountOwnerAssignReq);
            }
        }

 private void MergeFailedContacts(SDK.Contact subOrdinateContact, SDK.Contact masterContact, SDK.Contact updateContent)
        {
            IOrganizationService service = CRMHelper.GetCRMConnection();

            OrgServiceWrapper orgServiceWrapper = (OrgServiceWrapper)service;
            //Get Organization Service Context User Credentials
            string strUserDomainName = orgServiceWrapper.ClientCredentials.Windows.ClientCredential.Domain + "\\" + orgServiceWrapper.ClientCredentials.Windows.ClientCredential.UserName;

            string userFetchXML = string.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true'>
                                                    <entity name='systemuser'>
                                                        <attribute name='systemuserid'/>
                                                        <filter type='and'><condition attribute='domainname' operator='eq' value='{0}' /></filter>
                                                    </entity>
                                                </fetch>", strUserDomainName);            
            //------------------
            //1-Get CRMS Service(Admin User) UserId for changing account owner
            //------------------
            var userFetchExp = new FetchExpression(userFetchXML);
            EntityCollection userEntityCollection = service.RetrieveMultiple(userFetchExp);
            if (userEntityCollection.Entities.Count > 0)
            {
                //------------------
                //2- Change Master Contact owner to CRMS Service(Admin User)
                //------------------
                AssignRequest cRMSServiceUserAssignReq = new AssignRequest
                {
                    Assignee = new EntityReference("systemuser", userEntityCollection.Entities[0].Id),
                    Target = new EntityReference("contact", masterContact.Id)
                };
                // Execute the AssignRequest
                service.Execute(cRMSServiceUserAssignReq);

                //------------------
                //3-Merge Contacts
                //------------------
                MergeRequest request = new MergeRequest();

                request.Target = new EntityReference("contact", masterContact.Id);
                request.SubordinateId = subOrdinateContact.ContactId.Value;
                request.UpdateContent = updateContent;

                MergeResponse response = (MergeResponse)service.Execute(request);

                //------------------
                //4- Change Master Contact Owner back to orginal owner who was before Merge
                //------------------
                AssignRequest contactOwnerAssignReq = new AssignRequest
                {
                    Assignee = masterContact.OwnerId,
                    Target = new EntityReference("contact", masterContact.Id)
                };
                // Execute the AssignRequest
                service.Execute(contactOwnerAssignReq);
            }
        }

Advertisement