More specifically - what happens is that the Sync job is trying to sync 2 records for the Transaction, one for each of the duplicate relationships and this can cause two types of problems:
1) The first row is processed successfully, clearing the error row, but the second one is invalid and is causing an error. The Sync then cannot find the error row to update because it was just cleared by the first record syncing successfully.
2) Sync tries to insert an error twice for the same record for the record, as a result of the duplicate relationship making the record return twice.
If this error is returned in the sync log, please create a support request at www.blackbaud.com/support providing the full text of the error message and referencing this solution.
Here are the steps to fix the error:
1. Pull out the values (starting with TAID1) found in the "Cannot insert duplicate key row in object 'dbo.B2TROWS' with unique index" line of the error log. In this case, it is as follows:
12037693, 2012-11-09 00:00:00, 2, 1
2. Identify the BBEC Table name from the INFO line in the error log. In this case, it is REVENUEMATCHINGGIFT:
3. Identify the TA Sync table to query for duplicates. The Table name is in this format: T2B_MAP_[BBEC Table]. In this case, it is T2B_MAP_REVENUEMATCHINGGIFT.
4. Construct a query for T2B_MAP_[BBEC Table]. Note that the date format is 'DD-MMM-YY'. Here is an example of the query:
WHERE TAID1 = 12037693
AND TAID2 = '09-NOV-12'
AND TAID3 = 2
AND TAID4 = 1;
If 2 records are returned, then you have verified the duplicate record that is causing the Sync error.
5. Using the TAID1 value for the Account_ID, construct a query to locate the 2 duplicate records:
FROM PBDS.RELATIONSHIPS R,
(SELECT ACCOUNT_ID, RELATED_ACCOUNT_ID, NAME_ID, RELATED_NAME_ID, RELATIONSHIP_TYPE, RECORD_SEQ
FROM PBDS.RELATIONSHIPS) R2
WHERE R.ACCOUNT_ID = 12037693
AND R.ACCOUNT_ID = R2.ACCOUNT_ID
AND R.RELATED_ACCOUNT_ID = R2.RELATED_ACCOUNT_ID
AND R.NAME_ID = R2.NAME_ID
AND R.RELATED_NAME_ID = R2.RELATED_NAME_ID
AND R.RELATIONSHIP_TYPE = R2.RELATIONSHIP_TYPE
AND R.RECORD_SEQ <> R2.RECORD_SEQ
ORDER BY R.ACCOUNT_ID, R.RELATED_ACCOUNT_ID, R.NAME_ID, R.RELATED_NAME_ID;
6. TA Support should inform the client of the duplicate record, by giving them the ACCOUNT_ID, RELATED_ACCOUNT_ID and RELATIONSHIP_TYPE.
7. The client should now go to the TA frontend and select one of the records to delete, and then set the Status to 'D' for that record.