Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Duplicate key -error w...
Finance | Project Operations, Human Resources, ...
Suggested answer

Duplicate key -error when exporting to BYOD

(3) ShareShare
ReportReport
Posted on by Migi 226
I have recently started to get the following error with recurring BYOD export: 
""Violation of PRIMARY KEY constraint 'PK_XXXStaging'. Cannot insert duplicate key in object 'dbo.XXXStaging'. The duplicate key value is (Export across company: XXX - 17.12.2024 10.24.47.-09C1857EBD804C81884EF1BA788EC8DA, 1.0000000000000000, 150-000002, initial).". Failed to insert record into staging table. The keys of the record are DEFINITIONGROUP, EXECUTIONID, XXX, PARTITION, XXX. Duplicate records must be removed from the file prior to import."
 
The entity being exported is a custom and it's been working fine so far. It stopped working the following day when Quality update of 10.0.41 was installed so, I assume something was changed there (but may also be coincidence). 
I have truncated the entity's staging table and republished entity to BYOD, but nothing works. It complains about duplicate event when I choose to export only one record! 
I have checked that the entity works fine (with odata query) - there are no duplicates. I also exported some rows to CSV files and they work fine. Also rows gets copied to staging table just fine, but the problem is the target SQL BYOD Staging-table - the final step of the process.
 
Have anyone solved this kind of situation earlier? 
Categories:
Dynamics 365 Finance
  • Migi Profile Picture
    Migi 226 on at
    Duplicate key -error when exporting to BYOD
    Sorry for the late reply. I finally got access and permissions to the BYOD SQL and changed the primary key index for the entity's staging-table to see what's going on there. 
    I then exported one single record to BYOD and got the same error again. This time (because of new index) it wrote 1400 lines to the table and all rows had the same values in fields. So basically it wrote the same record all over again. The only thing that changed was recid value. And once again all looked good in D365 staging table. There must be something wrong with the connection to BYOD SQL.  Any ideas?
    @Martin Dráb @Alireza Eshaghzadeh​​​​​​​
  • Suggested answer
    Alireza Eshaghzadeh Profile Picture
    Alireza Eshaghzadeh 13,264 Super User 2024 Season 2 on at
    Duplicate key -error when exporting to BYOD
    Hi,

    Do you know the exact record in the staging table that is causing the duplicate issue? If yes, a potential workaround could be to create a filter in the BYOD export job to exclude the problematic record.

    Here’s a suggested approach to resolve the issue:

    1. Truncate the staging table to clear the existing data.
    2. Deactivate change tracking for the entity.
    3. Unpublish the entity from BYOD.
    4. Refresh the data entity to ensure metadata is updated.
    5. Republish the entity to BYOD.
    6. Activate change tracking again for the entity.
    7. Create a new BYOD export job with the appropriate filtering criteria.
  • Martin Dráb Profile Picture
    Martin Dráb 230,569 Most Valuable Professional on at
    Duplicate key -error when exporting to BYOD
    One of my clients reported similar errors starting in 10.0.40, but in all cases, there really were duplicates, although not always obvious. It seems that the problem used to be silently ignored (the export ended with Partially succeeded status and no one payed attention to it), but it was made more disruptive in 10.0.40. 
     
    It's not clear whether your problem is the same, but it could be.
     
    You mentioned you got the problem even when exporting a single record. Please run a SQL query against the entity view and check that there are duplications any unique index of XXXStaging (usually just StagingIdx, but you might have created more). Maybe you'll get a different result than in your previous tests.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,883 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,569 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans