Introduction

In certain circumstances you may encounter an issue where Credit Hound will only run an optimised synchronisation, which does not synchronize updates to account contacts. Attempting to run a full synchronisation results in an error message as per the image below.

Problem

When running a full synchronisation the following error is displayed.

Resolution

This error can be caused by the Sage data containing one or more duplicated roles for one or more customer accounts. To identify which account is at issue, run the SQL query below against the Sage 200 database.

Back up the Sage 200 database before running this query. The query itself does not modify any data, but it may well lead to data needing to be deleted as a result.

SELECT
dbo.SLCustomerContact.SLCustomerAccountID,
SLCustomerAccount.CustomerAccountNumber,
dbo.SLCustomerContact.SLCustomerContactID,
dbo.SLCustomerContact.ContactName,
dbo.SLCustomerContact.FirstName,
dbo.SLCustomerContact.LastName
FROM
(
    SELECT  
    dbo.SLCustomerContact.SLCustomerContactID,  
    dbo.SLCustomerContact.SLCustomerAccountID
    FROM  dbo.SLCustomerContact LEFT JOIN dbo.Salutation ON (dbo.SLCustomerContact.SalutationID = dbo.Salutation.SalutationID), dbo.SLCustomerContactRole  
    WHERE dbo.SLCustomerContact.SLCustomerContactID = dbo.SLCustomerContactRole.SLCustomerContactID AND dbo.SLCustomerContactRole.SYSTraderContactRoleId = (
         SELECT dbo.SYSTraderContactRole.SYSTraderContactRoleID
         FROM dbo.SYSTraderContactRole WHERE dbo.SYSTraderContactRole.IsDefaultRole <> 0 )
    GROUP BY SLCustomerContact.SLCustomerContactID, SLCustomerAccountID
    Having Count(1) > 1
) DuplicateContactRoles
LEFT JOIN SLCustomerAccount ON DuplicateContactRoles.SLCustomerAccountID = SLCustomerAccount.SLCustomerAccountID
LEFT JOIN SLCustomerContact ON DuplicateContactRoles.SLCustomerContactID = SLCustomerContact.SLCustomerContactID