Kbase P19906: Errors 6182, 2651 and 142 when loading data into SQL Server/ODBC
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
28/09/2010 |
|
Status: Verified
SYMPTOM(s):
Errors 6182, 2651 and 142 occur when migrating a Progress database to a foreign database using the Schema Migration Utility
Errors 6182, 2651 and 142 are present within the *.e file created by migration utility
"You tried to compare or to update a character field with a value longer than the maximum length. (6182)
Field num: <field-num>, value: <value>. (2651)
Field num: 1, value: (2651)
** Unable to update <filename> Field. (142)
** Tolerable load error rate is: 0%.
** Loading table <table_name> is stopped after 1 error(s).
Load data option is selected when migrating
Error occurs after the definitions and tables are created and during loading of data.
No data loaded into table
FACT(s) (Environment):
OpenEdge DataServer Category: SQL Server
MS SQL DataServer
ODBC DataServer
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
CAUSE:
During the migration, the DataServer defines the length of the field within the foreign database based on the format definition from the Progress database.
For example, field-1 is defined as format x(4) in a Progress database. In Progress the format is how large the information is displayed not how big the record is physically stored. Thus it is possible to have a large record (i.e. 10K) but with display format as x(4).
When the Progress database is migrated, by design, the DataServer creates the length of field-1 in SQL Server as 4. This is the format display size within the Progress database.
The problem arises when the data in field-1 is larger than the defined length in the foreign database. During the migration, the schema is created first in the foreign data-source, the data is dumped, the schema is re-pulled back into the schema holder and then the data is loaded. During the load, if the data is too large to fit into the defined field then this set of error messages will be encountered.
FIX:
Option #1
1) Find out which field is causing the problem. Usually, error 2651 helps to identify the field. In this example, the field is 1, thus meaning the table had a problem loading the data into field-1.
Field num: 1, value: (2651)
2) Run a simple FOR EACH to figure out the length of the character string
FOR EACH TABLE
DISPLAY LENGTH (field-1).
END.
This will help you determine the length of a record.
3) Go into SQL Server and increase the length of field-1 to be equal or greater than the number obtained from step 2.
4) Run "Update/Add Table Definitions" utility on the affected table
5) Using the foreign database query tool, delete the data that may have loaded in the modified table. If the data is not deleted then duplicate record errors may occur for the unique index.
6) Reload the .d into SQL Server through the Data Dictionary/Data Administration.
7) Load completes
Option #2 - for 9.1D05 and later
1) Run DBTOOL option #2 against the Progress/OpenEdge database before migrating it.
2) In Progress / OpenEdge DB to MS SQL Server utility, opt to use the (SQL) Width for field widths rather than ABL format. This will ensure that the changes made by DBTOOL are reflected in the foreign database.