DB sync failed - table financialreports
DB sync failed due to error creating a unique index
CREATE UNIQUE INDEX I_4361FOLDERREPORTNAME ON DBO.FINANCIALREPORTS(PARTITION,DATAAREAID,FOLDER,REPORTNAME)
The duplicate key value is (5637144576, 0987, Default Report Definitions, 12 Month Rolling Single Column Income Statement - Default).
When applying application packages, DB sync failures typically occur during creation of unique index:
- New column (unique values) - if table has existing data, sync will try to create blank values in existing rows which will throw error
In my case, I got error in table finacialreports. However, data is correct. data is unique on columns "PARTITION,DATAAREAID,FOLDER,REPORTNAME, designID". But for some reason in our package, the clusteredindex doesn't have designname and hence, it is throwing errors for rows with different design name but same values for "PARTITION,DATAAREAID,FOLDER,REPORTNAME"
To resolve issue temporarily, I used below query to delete duplicate rows.
delete FROM financialreports
WHERE recID NOT IN (SELECT MAX(recID) as recid
FROM financialreports
GROUP BY REPORTNAME,DATAAREAID,FOLDER,PARTITION
/*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
nullable. Because of semantics of NOT IN (NULL) including the clause
below can simplify the plan*/
HAVING COUNT(REPORTNAME) > 1
-- HAVING MAX(recID) IS NOT NULL
)
Note: Before doing this, make sure you backup data for that table so that it can be used later.
