Duplicate Key error encountered during dataset conversion

Problem

While attempting a conversion, the process fails due to the following error:  "The record has a key field containing a duplicate value (Btrieve Error 5)." 

Solution

To better explain the resolution, we'll assume that BANKACCT.BNK is the table currently failing conversion.  This work should only be attempted with guidance from AGRIS support staff.  

1.  Go to the numbered dataset directory (i.e. 001) that is associated with the failed conversion and locate any *.STP files. (There should only be 1 *.STP file with a current date/time.  Backup/Remove any older *.STP files you find in that directory.)

2. Note the name of the STP file itself and open the file to determine which step number the process is failing on.  

3. Browse out to the AGRIS\SqlScripts\ (MSSQL or PSQL) and locate the file that matches the name of the STP file found previously (in our example, PSQL\BNK105000.sql)

4. After opening the *.SQL file in notepad, find the line number that matches up to the step number found in #2.  

5. After identifying the point of failure, you should know what table is having the problem.  Duplicate key errors involve the primary key (a grouping of columns that together ensure that each row in the table is unique) and the fact that multiple records have the same primary key.  The next step is to determine what columns represent the primary key for the table in question.  Since this is happening during a conversion, you will probably see in the *.SQL file what the system is attempting to set as the primary key.  (For BANKACCT.BNK, the primary key is B01_ref_num_s, B01_loc_cd_s, B01_cd_s, and B01_tran_dt_t)

6. Open the appropriate database admin tool–either Pervasive Control Center or SQL Server Management Studio (in our example, PCC). 

7. Run queries against the table in question for each of the columns that comprise the primary key, both ascending and descending.  You are looking for any records that appear to be junk.  In some cases you might see that multiple records have several columns of the primary key either blank or filled with invalid characters.  Such junk records are most likely to appear at the very top or bottom of the results and that is why each column is sorted both ways.  Here is an example for BANKACCT.BNK:

   1. select top 100 * from "B01_TRANS" order by B01_ref_num_s
      select top 100 * from "B01_TRANS" order by B01_ref_num_s desc
      select top 100 * from "B01_TRANS" order by B01_loc_cd_s
      select top 100 * from "B01_TRANS" order by B01_loc_cd_s desc
      select top 100 * from "B01_TRANS" order by B01_cd_s
      select top 100 * from "B01_TRANS" order by B01_cd_s desc
      select top 100 * from "B01_TRANS" order by B01_tran_dt_t
      select top 100 * from "B01_TRANS" order by B01_tran_dt_t desc

8. Delete all junk records manually. (In this example, you can use the mouse and the CTRL and/or Shift keys select multiple rows before selecting the Delete Rows option in PCC)

9. After all of the junk records are gone, go back to the *.STP and .SQL files.  In the SQL file there should be a reference to dropping what is presumably an temp table (TMP_***) usually around line #13 and that temp table should correspond to the where the failure is occurring.  Update the *.STP file to point to the line number earlier in the script where the temp table is dropped (we need to roll back in the installation routine–DO NOT MANUALLY ADVANCE TO A HIGHER NUMBER STEP).  

10. Attempt to complete the conversion process again.  

Related articles