CONVERSION: 'Record Length Validation Error'

Problem

Solution

To resolve the issue correctly, the data needs to undergo the missing conversion steps to bring it in line with the current structure of the respective database table using the following steps:    

 WARNING:  BEFORE CONTINUING, MAKE SURE YOU HAVE A BACKUP OF THE TABLE(S) IN QUESTION

 WARNING:  ONLY QUALIFIED AGRIS SUPPORT PERSONNEL SHOULD ATTEMPT THE REPAIRS INDICATED IN THIS DOCUMENT.  IN FACT, PSQL ADMINISTRATIVE CREDENTIALS ARE REQUIRED IN STEP 8 AND THOSE CREDENTIALS ARE NEVER PROVIDED TO CUSTOMERS AND/OR END-USERS. 

1. Determine how the difference between the expected and actual values.  This will help identify what version the data is in. 

   1. In the B01 example above, the difference is 200 characters.

2. Open the most recent ‘Table Changes’ document (i.e. AGRIS_xx_DB_Table_Changes.xlsx) from AGRIS_xx_Database_Documentation.zip) and locate the most recent changes for the table in question.  At this point, we need to determine exactly how those changes impacted the number of characters associated with the table in question. 

   1. If the ‘Table Changes’ documents list the change and indicate the number of characters associated with the column change, CONTINUE TO STEP #4. 

   2. If the ‘Table Changes’ documents list the change but do NOT indicate the number of characters associated with the column change, CONTINUE TO STEP #3. 

   3. If the ‘Table Changes’ documents do NOT list the change, then repeat this entire step using the next older ‘Table Changes’ document, which would be AGRIS_10_DB_Table_Changes.xlsx.  It may be necessary to reference even older versions of the ‘Table Changes’ document such as AGRIS_V96_DB_Table_Changes.xls if the AGRIS_10_DB_Table_Changes.xlsx version does not indicate the change.

      1. When the change is identified, CONTINUE TO STEP 2A OR 2B depending on if it does or does NOT cite the number of characters associated with the change.   

   4. Continuing our B01 example, we find that a new column (B01_JDE_SEND_S) was added in E1.1, but this document does not indicate the number of characters associated with the new column.  This aligns with 2B and indicates that we will need to continue to Step #3.

3. If the ‘Table Changes’ document shows a change but does not indicate the number of characters associated with the change, then it is necessary to review the corresponding ‘Database Documentation’ document (i.e. AGRIS_E2_DB_Definition.docx).  In the ‘Database Documentation,’ locate the table in question and identify how many characters are associated with the column.  This is generally found in the Data Type column, but it may be necessary for some instances to calculate the size of the column based on its position and the position of the next column.  CONTINUE TO STEP #4.

   1. Continuing with our example, we find that B01_JDE_SEND_S shows a Data Type of CHAR(1) indicating that this is an alphanumeric value that consists of 1 character.  We can also see that the Position value for B01_JDE_SEND_S is 317 and that the next column is B01_UNUSED_1_S and its position is 318.  The difference between these values indicates that the B01_JDE_SEND_S column has a length of 1 character.

4. Compare the value(s) found in Step 2 (OR 3 if step #3 was necessary) with the value in Step 1. 

   1. IF THE NUMBERS MATCH, then we would know that the table is simply missing the most recent conversion routine for that table.  CONTINUE TO STEP 5.

   2. IF THE NUMBERS DO NOT MATCH, then….  GO BACK TO STEP #2C AND REPEAT THE PROCESS USING THE NEXT OLDER VERSION OF THE ‘TABLE CHANGES’ DOCUMENT. 

      1. Though unlikely, it may be that multiple conversions/table changes were missed for the same table.  This means that it may be necessary to add together the number of characters associated with various table changes until the math works out.  

   3. For our B01 example, we find in AGRIS_10_DB_Table_Changes.xlsx that there was a change in 10.1.0 to expand B01_NAM_DESC_S to 75 characters and another change in 10.2.0 to expand B01_NAM_DESC_S to 250 characters.  We know the difference between the 1^st expansion and the 2^nd expansion is 175 characters, which gets us close to the 200-character difference we’re looking for (SEE IMAGE #1).  However, we don’t know how many characters the 1^st expansion actually added to the existing column.  AGRIS_V96_DB_Definition.doc shows detailed table information at the end of V9.6.0.14 (last version prior to V10.0.0).  In this document, we find that B01_NAM_DESC_S was only 50 characters long at that point in time (SEE IMAGE #2).  Based on this, we can feel confident that the 200-character discrepancy from the original error message is accounted for by the changes to the B01_NAM_DESC_S column in 10.1.0 and 10.2.0.

5. At this point in the process, we should know:

   1. Exactly what table change(s) we have missed

   2. The number of characters associated with the missing table change(s) matches the discrepancy between expected/actual that was identified in Step #1. 

   3. If 5A and 5B are TRUE, then continue to Step #6.  Otherwise, return to earlier steps in the process until 5A and 5B are true. 

6. Next, we’ll need to locate the conversion routine that is tied to the missing table changes:

   1. Browse out to the AGRIS\SqlScripts\PSQL directory (It may be helpful to change the view to ‘Details’ and then sort alphabetically by the Name column). 

   2. Find the script(s) that contain the conversions that were missed: 

      1. The script names all begin with the package reference, which is followed by the version number.  The version number corresponds to the conversion routine that was completely or partially skipped. 

   3. Continuing our B01 example, we are looking for the BNK101000.sql (containing the V10.1.0 change where B01_NAM_DESC_S was expanded by 25 characters) and BNK102000.sql (containing the V10.2.0 change where B01_NAM_DESC_S was expanded by an additional 175 characters).  

7. Examine the script(s) to see if they reference only the table in question.

   1. If YES, then we can run the script(s) in their entirety within PSQL Control Center.  CONTINUE TO STEP #8.

   2. If NO, then the safest approach is to run only the table change we need to bring the table back into alignment.  One approach would be to open notepad and copy/paste in only the necessary changes.  Then proceed to step #8 and run these items within PSQL Control Center. 

      1. NOTE:  There may be additional elements of the scripts that need to be run, but for now, stay focused on the table we know to have issues to avoid confusion. 

   3. In our B01 example, we find that both scripts deal exclusively with changes involving the B01 table, which aligns with 7A (SEE IMAGE #3).

8. Make the necessary table changes via PSQL Control Center 

   1. Open PSQL Control Center

   2. Expand Engines > (Server Name) > Databases

   3. Right-click the database in question and select ‘Login…’

   4. Enter the necessary credentials make changes in the database

      1.  NOTE:  For PSQL, these credentials are NEVER disclosed to customers or end-users.  Only authorized AGRIS support technicians should make the changes described in this document.  

   5. Browse to the table in question and double-click it.

   6. Replace the ‘select * from …’ text with the missing conversion commands that were previously identified in Step #7. 

   7. Click SQL > Execute in grid or click the Execute in grid button at the main menu (SEE IMAGE #4).

      1. If multiple scripts need to be executed, either in part or in full, it is advisable to complete to earliest script first, observe that the commands executed successfully, and then continue that routine for any remaining scripts. 

   8. If the necessary commands completed successfully, CONTINUE TO STEP #9.  Otherwise, it may be necessary to troubleshoot the commands to determine why they are unable to run as expected. 

9. Retry the conversion operation in AGRIS.  Ideally, the conversion will make continue past the original validation error.  Be aware that there may still be other tables and or databases, that experience similar issues and require repeating the contents of this document to bring those tables into alignment as well. 

IMAGE #1:

Open

IMAGE #2:

Open

IMAGE #3:

Open

IMAGE #4:

Open

Related articles