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

Created: 13 Nov 2014
Last Updated: 07 Oct 2015


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.
Message Image


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 no Stock error or Customer Account on hold 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 than 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.


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

Search Again

See Also...

l: 0844 357 7360 | i: +44(0)344 357 7360 | e:
© Hilltops IT Consultancy Services Ltd 2005-2022

Time saving solutions from Hilltops IT