Export from progress to SQL server Database - Error messages - Forum - OpenEdge RDBMS - Progress Community

Export from progress to SQL server Database - Error messages

 Forum

Export from progress to SQL server Database - Error messages

This question is answered

Hi - I am a progress novice and have what is probably a really basic question.

We have a progress database as a standalone application and a whole load of SQl Server databases that rely on teh information in the progress system for more accurate information. We have OpenEdge driver 10.2B. I use a SQL DTS (becasue I am stuck on a SQL 2000 system(!)) to transfer the data.

The DTS normally works brilliantly. However, sometimes it seems that the progress database is allowing a user to enter a larger entry than the field size would suggest and I get error messages that state, for example:

[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Column X in table Y has value exceeding its max length or precision.
[DataDirect][ODBC Progress OpenEdge Wire Protocol driver] Error in row.

Is this a matter of the driver looking at the database and doing DQ checks before pumping the information across? Brilliant but irritating that the Progress system allows the inital entry.

If, in my DTS, I exclude a field, will the OpenEdge driver still look at the field for DQ checks before pumping uout a reduced dataset?

I hope I haven't rambled too much and that someone can answer. I have asked the system supplier and they were unsure.

Verified Answer
  • Hi,
     
    Progress database allows users to enter larger value than field size. Even though you have excluded the field, OpenEdge SQL validates length and reports an error.
    You can fix the schema metadata using db utility.
     
    And also you can restrict that (Don’t allow more than field size) by specifying startup  –checkwidth parameter.
     
    Thanks and Regards,
    Chandu
     
    From: realityleak [mailto:bounce-realityleak@community.progress.com]
    Sent: Friday, March 07, 2014 3:49 PM
    To: TU.OE.RDBMS@community.progress.com
    Subject: Export from progress to SQL server Database - Error messages
     
    Thread created by realityleak

    Hi - I am a progress novice and have what is probably a really basic question.

    We have a progress database as a standalone application and a whole load of SQl Server databases that rely on teh information in the progress system for more accurate information. We have OpenEdge driver x.yb. I use a SQL DTS (becasue I am stuck on a SQL 2000 system(!)) to transfer the data.

    The DTS normally works brilliantly. However, sometimes it seems that the progress database is allowing a user to enter a larger entry than the field size would suggest and I get error messages that state, for example:

    [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Column X in table Y has value exceeding its max length or precision.
    [DataDirect][ODBC Progress OpenEdge Wire Protocol driver] Error in row.

    Is this a matter of the driver looking at the database and doing DQ checks before pumping the information across? Brilliant but irritating that the Progress system allows the inital entry.

    If, in my DTS, I exclude a field, will the OpenEdge driver still look at the field for DQ checks before pumping uout a reduced dataset?

    I hope I haven't rambled too much and that someone can answer. I have asked the system supplier and they were unsure.

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

All Replies
  • >> IIRC, dbtool sets sql_width to twice the width of the widest instance if the field in question

    Until now i thought it sets the width to the maxium value (equal) and there is an option to set it higher than max in percent.

    Intial width is set by data dictionary to twice of width.

    Is this new with 11.x?

  • no, it's been that way since at least 10 .. probably 9

    Julian

    On 10 March 2014 12:06, Stefan Marquardt
    wrote:
    > RE: Export from progress to SQL server Database - Error messages
    > Reply by Stefan Marquardt
    >
    >>> IIRC, dbtool sets sql_width to twice the width of the widest instance if
    >>> the field in question
    >
    > Until now i thought it sets the width to the maxium value (equal) and there
    > is an option to set it higher than max in percent.
    >
    > Intial width is set by data dictionary to twice of width.
    >
    > Is this new with 11.x?
    >
    > Stop receiving emails on this subject.
    >
    > Flag this post as spam/abuse.



    --
    Julian Lyndon-Smith
    IT Director,
    dot.r
    http://www.dotr.com

    "The bitterness of poor quality remains long after the sweetness of
    low price is forgotten"

    Follow dot.r on http://twitter.com/DotRlimited
  • >>> IIRC, dbtool sets sql_width to twice the width of the widest instance if

    >>> the field in question

    I just run a test with 10.2A:

    Twice a value would be every time an even value but it's odd.

    Current max fieldLen for T02Accident (27):

    Fld#  SQLWidth  Max Width  ERROR NAME

    ----  --------  ---------  ----- ----                          

     4:       200        245    *** AccidentName

    After running dbtool it's:

    Current max fieldLen for T02Accident (27):

    Fld#  SQLWidth  Max Width  ERROR NAME

    ----  --------  ---------  ----- ----

     4:       245        245        AccidentName

    So 10.2A dbtool sets sql-width to max.width or something goes wrong on my side ...

    Stefan

  • by default, the data dictionary sets the sql width to be 2x format width

    I think that the dbtool sets the sql width to be the size of the
    largest field size (see
    http://knowledgebase.progress.com/articles/Article/P162121). The
    max-width is the largest data field size.

    One of the options when running dbtool is the "padding" (Padding %
    above current max: 100 for example ) This allows you to specify how
    much "padding" (in % terms) to add on, so 100% is 2xmax field size.

    http://stackoverflow.com/questions/11998342/fixing-sql-length-error-in-progress-4gl-10-2b



    Julian

    On 10 March 2014 12:57, Stefan Marquardt
    wrote:
    > RE: Export from progress to SQL server Database - Error messages
    > Reply by Stefan Marquardt
    >
    >>>> IIRC, dbtool sets sql_width to twice the width of the widest instance if
    >
    >>>> the field in question
    >
    > I just run a test with 10.2A:
    >
    > Twice a value would be every time a even value but it's odd.
    >
    > Current max fieldLen for T02Accident (27):
    >
    > Fld# SQLWidth Max Width ERROR NAME
    >
    > ---- -------- --------- ----- ----
    >
    > 4: 200 245 *** AccidentName
    >
    > After running dbtool it's:
    >
    > Current max fieldLen for T02Accident (27):
    >
    > Fld# SQLWidth Max Width ERROR NAME
    >
    > ---- -------- --------- ----- -
  • Juian,

    sorry for the confusion but that's what i wrote ...

    >>> IIRC, dbtool sets sql_width to twice the width of the widest instance if

    >>> the field in question

    >

    > Until now i thought it sets the width to the maxium value (equal) and there

    > is an option to set it higher than max in percent.

    Stefan

  • yeah, we seem to be going round in circles ;)

    I always thought that dbtool added padding by default, from 9 onwards..

    On 10 March 2014 13:15, Stefan Marquardt
    wrote:
    > RE: Export from progress to SQL server Database - Error messages
    > Reply by Stefan Marquardt
    >
    > Juian,
    >
    > sorry for the confusion but that's what i wrote ...
    >
    >>>> IIRC, dbtool sets sql_width to twice the width of the widest instance if
    >
    >>>> the field in question
    >
    >>
    >
    >> Until now i thought it sets the width to the maxium value (equal) and
    >> there
    >
    >> is an option to set it higher than max in percent.
    >
    > Stefan
    >
    > Stop receiving emails on this subject.
    >
    > Flag this post as spam/abuse.



    --
    Julian Lyndon-Smith
    IT Director,
    dot.r
    http://www.dotr.com

    "The bitterness of poor quality remains long after the sweetness of
    low price is forgotten"

    Follow dot.r on http://twitter.com/DotRlimited
  • The 4GL does not impose a max width on varchar columns, only on the total row size. This is different from SQL VARCHAR, which does have a maximum size. The max width used by the OpenEdge SQL is derived from the default /display/ format. But it is just a default for display. As some else noted previously, you can make the 4GL runtime enforce the max width setting if you choose to, but most people do not.

  • hi paul. i look forward to seeing your mathematics.