RFC downloads behave differently from downloads that pull data from the database layer. Whether RFC downloads are using Syniti tools or not, they connect to the application layer, which makes it possible to find duplicates as a result of uncommitted records in the application layer.
Uncommitted records can sometimes be the result of SAP functionality: if, for any reason, SAP can't commit changes made by users or function modules, SAP will keep the uncommitted records in the application layer until it can commit them. The duplicates in the SAP extraction are not apparent in database tables or in the SAP SE16 transaction code; however, the duplicates can be manually removed.
There are two workarounds to remove the duplicates. 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