Error during retrieve from Sage 300 to Sales Analysis

Question

During an end of month retrieve into SA the client is getting the following error message: 
 [Microsoft][SQL Server Native Client 11.0][SQL Server] Cannot insert duplicate key row in object 'dbo.UXARINVHO' with unique index 
 'UXARINVHO_idx0'. The duplicate key value is (6, 1, SALESMAN ) 
 What's wrong? What do I need to do to fix? 
 Any and All Advice Appreciated, 
 
 Dana

Dana B. Stidsen · Accepted Answer

This client is on v2019. I looked in the database and the duplicate data it complained about in the error message is from 2007. The year 2007. Batch 6, entry 1. Doesn't make sense. 
 I ran the retrieve my self and it completed the retrieval from OE. When it started AR it stopped and told me that an interest entry, something like INT0000015, was already in the data, do you want to skip it? 
 I said yes and it completed without issue. 
 I'm closing this case. 
 Dana

Brandonjhash · Answer

The error tells you exactly what's wrong. Look at the definition of the index dbo.UXARINVHO . This will tell you which Field you are having a problem with. The error message is telling you that the value you are trying to insert is the value " (6, 1, SALESMAN ) ". A unique index will prevent duplicate values for that row from being inserted. So you will either need to a) change the value to something that is unique or b) change the index so that it doesn't have a unique constraint. 99% of the time the correct coarse of action will be a).

Dana B. Stidsen · Answer

In my case there isn't much AR. It retrieves mostly from OE. 
 I'm pretty sure that Sales Analysis stores the record it finished with last time it retrieved. It saves the day end sequence or something like that so it doesn't try to read ALL the data next time it retrieves. It's likely that in your case that saved record has been changed somehow so it's trying to read the same records over again. 
 Locate where that's saved and I think you'll solve the issue. 
 Dana

Sage 300

Error during retrieve from Sage 300 to Sales Analysis

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING