Sage 100

Client is 2023.2 Premium with several DSD mods including SOIP which is pricing by ship to address and multi-currency. We had to reinstall all their mods and since then, IM_PriceCode will allow new records but any attempt to change an existing record generates "Error 15 - Operating System Command Failed - Violation of Primary Key constraint 'KPRIMARY_IM_PriceCode'. Cannot insert duplicate key in object 'dbo.IM_PriceCode'. The duplicate key value is (1,,Test2,6,,,USD,).  I've checked to make sure the primary keys are established (PriceCodeRecord, PriceCode, ItemCode, CustomerPriceLevel, ARDivisionNo, CustomerNo, IMMC_234_CurrencyCode, OIP_234_ShipToCode) and they look okay. I don't know why SQL is trying to add a new record instead of modifying the existing one. Another issue is that the change to a price does appear to be applying even though the error happens, unfortunately it is applying to every single existing price level for all items and customers, not just the one it is supposed to be changing. And just to make it extra exciting, this customer has 3.8 million pricing records. Does anyone have any idea where I should look next? 

------------------------------
Kate Krueger
Consultant
MicroAccounting
214.800.5555x553
------------------------------

My first stop would be asking DSD... their support is usually quite good with things like this, where their enhancements are clearly involved.  (It could be something simple like the order you install the enhancements needs to be done a certain way...).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Look at the DSD new fields in the key and see if any of the values are null, or maybe the code is trying to insert null values.  ProvideX seems to allow nulls in keys, but SQL absolutely does not.

" I don't know why SQL is trying to add a new record instead of modifying the existing one."  - Updates actually consist of a delete and an insert.  This makes both sets of values available to triggers.

------------------------------
Phil McIntosh
Friendly Systems
------------------------------

@Kevin Moyes - I agree that DSD support is the way to go! Jon connected to the client and the data dictionary didn't match the physical file. Running the DSD Enhancement Control Panel > Convert appeared to fix this for a test company. Will try this after hours with the live company.

@Phil McIntosh - Good information with what SQL is actually doing when modifying a record - Thank you!

------------------------------
Kate Krueger
Consultant
MicroAccounting
214.800.5555x553
------------------------------