Syniti has identified two workarounds to remove the duplicates if they are reported with an RFC extract in Collect. You can either:
- Pull the failing tables through ODBC connections to the database layer, or
- Set up rules in Collect that:
- Drop the Primary Key before running the refresh rule.
- Remove duplicates after running the refresh rule.
- Add the Primary Key after running the refresh rule.
For more information about registering rules to tables in collect, refer to Register Rules to Tables in the online help.
To extract the drop and create statements used to remove duplicates:
- Right-click the primary key in SQL Server Management Studio and select Script Key as > DROP to > your preferred output.
- Right-click the primary key in SQL Server Management Studio and select Script Key as > CREATE to > your preferred output.
An example of code to remove duplicates for MARA:
--identifies duplicates
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY MANDT, MATNR ORDER BY MANDT, MATNR) AS RN
FROM MARA
)
SELECT * INTO MARA_BACKUP FROM CTE WHERE RN<>1;
--removes duplicates
WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY MANDT, MATNR ORDER BY MANDT, MATNR) AS RN
FROM MARA
)
DELETE FROM CTE WHERE RN<>1
--removes MARA_BACKUP so it can be selected into the next time the table is refreshed
DROP TABLE MARA_BACKUP