Tried to compare / update a character field with a value longer than the max length

F

Fatboy40

New Member
We use an ERP product called Vantage from a company called Epicor, of which you can use two different DB's to store your data, Progress or MS SQL. We are on version 8 of this software and are moving to version 9, and at the same time moving from Progress to MS SQL. Even is you use MS SQL there's still a Progress schema holder DB that's used (along with Open Edge 10).

A few years ago, before I joined the company I'm at now, someone took it upon themselves to extend the length of some fields in some of the tables in the Progress DB, against every possible reccomendation from the ERP software vendor. As part of the move from Progress to MS SQL to store the data we dump the tables from Progress using the OE Data Administration tool to ASCII files (x.d files), and then on the MS SQL server we connect to the Progress schema holder (and the ODBC DSN for MS SQL) and then use the same tool to load the tables.

So when I load the tables I get for example this in an error file...

>> ERROR READING LINE #14 (Offset=17165): "You tried to compare or to update a character field with a value longer than the maximum length. (6182)
>> ERROR READING LINE #14 (Offset=17165): Field num: 31, value: 0208 8756 8000 (Ext 43167) . (2651)
>> ERROR READING LINE #14 (Offset=17165): ** Unable to update Customer Field. (142)

... the ERP vendor is moving slowly to assist me with this so I was wondering if anyone here has any ideas as to what I can do here ?.

It's easy to alter the field/column in MS SQL to increase the length, but how would I do this in the Progress schema holder DB ?.

Thanks.
 
Re: Tried to compare / update a character field with a value longer than the max leng

You run "dbtool". Dbtool will scan the database and adjust the SQL-Width attributes of the Progress data to reflect reality.
 
Re: Tried to compare / update a character field with a value longer than the max leng

Hi Tom,

Thanks for the reply. I don't think dbtool will help us at this moment in time, the Progress schema holder DB contains no data so there's nothing to scan against.

We're loading tables through it to an MS SQL DB, load Open Edge Data Aministration + connect to the Progress schema holder DB and the 'pf' file that the settings for the DSN for the MS SQL DB are held in, then load all the dumped ASCII tables.

So the fields in the Progress schema holder, and MS SQL DB, need to be extended first ?.

Thanks.
 
Re: Tried to compare / update a character field with a value longer than the max leng

Adjusting the SQL-Width is common so don't feel bad about the previous DBA having to change them :) . It is technically only the width presented to external (non-4GL) connections.

Can you run dbtool on the original progress database?

If not you will just have to adjust the SQL-Width each time. I do it like this (of course check with Epicor first to ensure this is allowed! Chances are they will ask for a copy of your database -- but which one do you send!!)
Proenv
# prompt
# cd /d e:\path_to\schema_holder_database
# prowin32 mfgsys -rx
specify the sql-92 DBA account if it exists (sysprogress?)
click options --> data dictionary
search for the customer table in the middle list
click options --> adjust field width
change the "width" column to be at least the longest value you have, I usually add 10% then round up to a nice big whole number
click save and then click close
click edit then commit transaction

I'm interested in how you get around this problem with the data migration as we may be faced with this some time in the future.

Good luck
 
Re: Tried to compare / update a character field with a value longer than the max leng

If you are able to run 4gl commands and you have a large number of fields to adjust, you can set the _width value in the _field table in a loop.

find the _file you want.
Loop through the _field of _file
adjust _width
 
Re: Tried to compare / update a character field with a value longer than the max leng

You don't mention the actual sizes involved. There are some differences between Progress and other databases in the size of character fields. If the SQL field is defined as one of the small ones, it might have trouble with the amount of data in one of the Progress ones.
 
You must log in or register to reply here.
Back
Top