Cant Load DB on selecting different collation than Latin 1 General, due to duplicate UOM defined per Items

By | May 24, 2011

While browsing through the Accpac Forum today, we came across an interesting post regarding ACCPAC Collation Method. We had faced the same problem in our development projects and we are sure one of you must have too, so this blog.
If you are trying to do a DbLoad from a Data Dump that used collation Latin 1 General, and are getting below error because you have duplicate UOM’s per item which violates data integrity of your new DB which has a different collation (case insensitive collation).

What you can do is before taking the Data Dump, Query the ICUNIT table in SQL to get the list of duplicate UOM, alternatively you can also write a Macro (if you know programming or we are always there 🙂 that will figure out the duplicate UOM’s for items. Once you have the list of duplicates, go ahead and delete extra records (make sure you take proper backup of your system first), then take the Data Dump and try to load the data in case insensitive collation environment.

This should solve the problem.

You can use following query to find out duplicate UOM assigned to item.
SELECT ITEMNO,UPPER(UNIT), COUNT(UPPER(ITEMNO)) FROM ICUNIT
GROUP BY ITEMNO,UPPER(UNIT)
HAVING COUNT(ITEMNO) >1
For any queries or more information, please drop us an email at sage@greytrix.com.