Support
Support

000354 : ConnectIt-Sage200 - cannot insert duplicate key row in object

Created: 07 Oct 2015
Last Updated: 12 Oct 2015

Message

Cannot insert duplicate key row in object ‘dbo.SOPOrderReturn’ with unique index ‘IX_SOPOrderReturn_DocumentTypeID_DocumentNo’. The duplicate key value is (0, ). The statement has been terminated.

Cause

When ConnectIt creates a Document in Sage through the Sage 200 API a placeholder record is created in the Sage database. This placeholder doesn't have a Document Number (DocumentNo) assigned initially - it gets applied when the record is committed to the database fully. If ConnectIt was committing a Document to the database (perhaps a Supplier Account On Hold or exceeded Credit Limit message - something ConnectIt doesn’t validate for directly but which Sage 200 would have raised a valid, meaningful message for as a prompt to the User) and the User cancelled the process, then potentially this placeholder could be left in the database. If the User had terminated ConnectIt at that time (rather than just clicking ok) then potentially ConnectIt wouldn't have disconnected with the Sage 200 API gracefully and so the placeholder record could have been left. It is this Document Number-less record that is now causing the ‘duplicate key row...’ error as ConnectIt (via the Sage 200 API) tries to create another Document Number-less placeholder record for the next Document the User tries to create.

Resolution

Use the ConnectIt Configuration | Advanced tab | Tools tab | Delete Document feature with ‘Purchase Order’ selected in the dropdown to remove the placeholder record.

Search Again

See Also...

l: 0844 357 7360 | i: +44(0)1273 013695 | e: enquiries@hilltopsit.co.uk
© Hilltops IT Consultancy Services Ltd 2005-2025

Time saving solutions from Hilltops IT