I sometimes get a "inserting duplicate key" error from my database when I use the MAINTAIN command update, like this --

reposition drug_benefit;
for 1 Update
DRUG_BENEFIT.drug_benefit.ES_ASSIGNED_USERID
DRUG_BENEFIT.drug_benefit.ES_ASSINGED_DT
DRUG_BENEFIT.drug_benefit.DRUG_PROGRAM_KEY
DRUG_BENEFIT.drug_benefit.DRUG_BENEFIT_START_DT
DRUG_BENEFIT.drug_benefit.DRUG_BENEFIT_END_DT
DRUG_BENEFIT.drug_benefit.BENEFIT_STATUS_KEY
DRUG_BENEFIT.drug_benefit.UPDATED_BY_USERID
DRUG_BENEFIT.drug_benefit.EFFECTIVE_START_DT
DRUG_BENEFIT.drug_benefit.EFFECTIVE_END_DT
from StkDrugBenefitEdit(1);

In some places in my code it works like a dream, in others I get a very dependable duplicate key error. Is there a super-secret trick to avoiding this odd behavior? Naturally I'll understand if you choose to send it to me via private message.

J.

This message has been edited. Last edited by: Kerry,

Huh. Maybe if the key is a character value? This may be a SQL Server problem.

J.

John - I can honestly say that I have never received that error when updating a database with Maintain.

Does the file contain key fields? Maintain does require one or more key fields in the file, listed at the top of the Master File Description.

If this file has a read-only, or auto-increment field (Fieldtype=R) then you have to take that into account when updating / including data.

If this is something you can repro at will, I would love to see the Master / access file.

Thanks
Mark

I fixed it this time by having the DBA revise the key to an integer from a char. That may be the heart and soul of the issue, as SQL Server seems to have some real problems with character-based keys. Maintain may have just been returning the errors it was given by the dbms -- being a good soldier.

J.