000349 : ConnectIt-Sage200 - cannot insert duplicate key row in object
|
| Created: |
18 Jun 2015 |
| Last Updated: |
18 Jun 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 |
Please refer to ConnectIt Knowledgebase Article 312 before continuing.
The utility referred to in Article 312 was added to the ConnectIt-Sage200 for Sage 200 2013 version in v1.10.1. If you do not have this feature available, then you may be running an earlier version of ConnectIt. In the abscence of this feature, while your Sage 200 support expert may know of alternative methods, the only way around it that we know is to edit the database tables to remove the placeholder record(s) that the Knowledgebase Article 312 refer to.
Below is a SQL Server database script which will remove the record (and those which may have been created that are related to it). PLEASE NOTE that this script is supplied without warranty or guarantee; extreme care should be taken when executing the script – please ask all Users to log out of Sage, take a complete backup of you database and ensure that a SQL / database / IT expert reviews the script thoroughly before running it. Once executed, then check for any errors before letting Users log back into Sage again.
|
Resolution |
USE Sage200_DemoData
DECLARE @DocNo varchar(20)
SET @DocNo = ''
DELETE
--SELECT COUNT(*)
FROM SOPOrderLineProfitAnalysis WHERE (SOPOrderReturnLineID IN (SELECT SOPOrderReturnLineID FROM SOPOrderReturnLine AS RL
INNER JOIN SOPOrderReturn AS R ON (RL.SOPOrderReturnID = R.SOPOrderReturnID)
WHERE (DocumentNo = @DocNo)))
DELETE
--SELECT COUNT(*)
FROM SOPAllocationLine WHERE (SOPOrderReturnLineID IN (SELECT SOPOrderReturnLineID FROM SOPOrderReturnLine AS RL
INNER JOIN SOPOrderReturn AS R ON (RL.SOPOrderReturnID = R.SOPOrderReturnID)
WHERE (DocumentNo = @DocNo)))
DELETE
--SELECT COUNT(*)
FROM SOPStandardItemLink WHERE (SOPOrderReturnLineID IN (SELECT SOPOrderReturnLineID FROM SOPOrderReturnLine AS RL
INNER JOIN SOPOrderReturn AS R ON (RL.SOPOrderReturnID = R.SOPOrderReturnID)
WHERE (DocumentNo = @DocNo)))
DELETE
--SELECT COUNT(*)
FROM SOPOrderReturnLine WHERE (SOPOrderReturnID IN (SELECT SOPOrderReturnID FROM SOPOrderReturn WHERE (DocumentNo = @DocNo)))
DELETE
--SELECT COUNT(*)
FROM SOPOrderProfitAnalysis WHERE (SOPOrderReturnID IN (SELECT SOPOrderReturnID FROM SOPOrderReturn WHERE (DocumentNo = @DocNo)))
DELETE
--SELECT COUNT(*)
FROM SOPDocDelAddress WHERE (SOPOrderReturnID IN (SELECT SOPOrderReturnID FROM SOPOrderReturn WHERE (DocumentNo = @DocNo)))
DELETE
--SELECT COUNT(*)
FROM SOPOrderReturn WHERE (DocumentNo = @DocNo)
|
Search Again
|
See Also...
|
|
|
