Export from progress to SQL server Database - Error messages

Posted by realityleak on 07-Mar-2014 04:18

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.

Posted by Chandra Sekhar on 07-Mar-2014 04:42

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
 
[collapse]
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.

[/collapse]

All Replies

Posted by James Palmer on 07-Mar-2014 04:41

I don't know for sure, but I think this is the SQL Width on the Progress side that is causing the errors. You will need to run DBTool to update this.

Posted by Chandra Sekhar on 07-Mar-2014 04:42

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
 
[collapse]
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.

[/collapse]

Posted by realityleak on 07-Mar-2014 05:17

Thanks both for responding - coming from a SQL server background (and not wanting to sound like a Microsoft snob) it goes against every DB design principle I have ever been taught that a DB can, basically, ignore table design.

Is there an actual purpose to this? or is it a feature of the software? The reason our supplier is using is that users like the flexibility, but if it makes the DB inefficient then it is really counterproductive...

Posted by James Palmer on 07-Mar-2014 05:49

It isn't ignoring the table design as such. The table is created with a best guess at the width. You are then provided with a way to expand it based on data added subsequently. If you're using SQL to access a Progress database there are a couple of things you should do reasonably regularly. One is to update the SQL widths with DBTool. The other is to update statistics so that the queries run more efficiently.

Posted by Paul Koufalis on 07-Mar-2014 07:29

All Progress CHAR fields are VARCHAR with a max size of 32K.  Since SQL absolutely wants a field width, Progress sets that using a special field attribute called sql_width. dbtool is the program provided by Progress to automatically adjust the SQL width attribute.  IIRC, dbtool sets sql_width to twice the width of the widest instance if the field in question.

I typically run dbtool and update statistics once per month.

Posted by James Palmer on 07-Mar-2014 08:21

Some would say Update Statistics should be run weekly, but I guess it's a business decision that has to be taken there.

Posted by Paul Koufalis on 07-Mar-2014 08:26

I used to run it weekly and I'm sure it's not a bad thing per se, but I doubt that data distributions change enough from week to week to justify it.  There may be a couple of tables that see a lot of ADD/DELETE action that would benefit from frequent UPDATE STATISTICS, but most tables (think customer, item, etc...) would not.

Maybe I'm wrong.  I'd be happy to hear someone with a mathematical explanation of how to calculate update statistics frequency requirements.

Paul

Posted by James Palmer on 07-Mar-2014 08:30

I'm just talking from the perspective of hearsay as we don't have SQL clients. A couple of folks over on Progresstalk champion the weekly run. Maybe they'll jump the great divide to fill us in here! ;)

Posted by ChUIMonster on 07-Mar-2014 08:38

The idea that field width ought to be limited for efficiency reasons is one of those ancient premature optimization things that doesn't actually hold water.  It's actually kind of surprising (to me anyway) that Codd & Date didn't put up a stink about it because it really doesn't fit with "relational" thinking very well at all.

Progress has had variable width fields from the beginning.  Progress also had row level locking from the beginning.  Just two of the ways that Progress has always been out ahead of the pack :)

Posted by Paul Koufalis on 07-Mar-2014 08:38

No worries James - my comment was not meant as an attack on you and I apologize if it came across that way.

Hearsay is one of those dangerous things: it's the same hearsay that tells us that variable length database extents are BAAAAAADDDD!!!!!!!!   (Hint: they are not 99.9% of the time and I can prove it mathematically)

Paul

Posted by James Palmer on 07-Mar-2014 08:42

Oh crumbs no Paul - no offence taken in the slightest! :)

As for hearsay - I completely agree. It depends, though, on whom the advice comes from - and in this case the particular folks on Progresstalk are folks whose advice I would trust wholeheartedly.

Posted by ChUIMonster on 07-Mar-2014 08:46

Rich has said that you should run UPDATE STATISTICS when 20% of your data has changed.  He then asks, rhetorically, how you can know that 20% of your data has changed...  (I suppose you could keep track of bi cluster closed?)

Personally, I often suggest weekly because:

1) It does no harm.

2) Most people have a slow time at least once a week when it is convenient to do so.

3) It is easy to script and there are several other things that I like to do weekly (dbtool, truncate logs, run dbanalys).

4) Weekly routines are less likely to be forgotten than monthly or even more irregular tasks.

Posted by realityleak on 07-Mar-2014 08:49

Not particularly query efficiency, more like storage space efficiency. If I was going to design a SQL database like that I would be using VARCHAR(MAX) field sizes which would increase my file size because of the memory each field would use. If you have ever seen the number of columns in any of the datasets that exist in the NHS, you would realise why sometimes limits in SQL Server have to be set. Integration with other DB systems is also more problematic if the DB is saying one thing but the data is saying another.

Still, Who's counting. They all have their place. Thanks for the help you all gave earlier and sorry if I have started any arguments :)

Posted by Paul Koufalis on 07-Mar-2014 08:55

That 20% is applicable at the table level more than the DB level, I would think.  Customer and item are unlikely to change 20% very often but inventory, shipper, invoice, gl...all those tables are constantly being modified.  

(Theoretically) I wonder if basing the decision on index operations (_IndexStat) would give a more precise trigger.  A table that has x amount of write activity to indexes is potentially in need of an UPDATE STATISTICS.

But you are correct, you script it once and forget about it.  I think monthly is more than sufficient but as you say, weekly does no harm and you are probably running other maintenance tasks weekly anyways.

Paul

Posted by ChUIMonster on 07-Mar-2014 08:56

I think that you are assuming that a field is padded out to it's maximum size.

Progress fields are not.  So they only take up whatever space they actually need.  That's actually pretty efficient from a disk and memory usage perspective.  Sure, the "overstuffed" fields use more space -- but the "understuffed" ones conserve a whole lot.

It is one of those reasons that Progress tends to be a lot less demanding of system resources than Oracle or SQL Server.

Arguments are good -- they bring out lots of discussion points :)

Posted by Stefan Marquardt on 10-Mar-2014 07:05

>> 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?

Posted by jmls on 10-Mar-2014 07:09

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

Posted by Stefan Marquardt on 10-Mar-2014 07:56

>>> 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

Posted by jmls on 10-Mar-2014 08:10

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
>
> ---- -------- --------- ----- -

Posted by Stefan Marquardt on 10-Mar-2014 08:14

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

Posted by jmls on 10-Mar-2014 08:30

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

Posted by gus on 10-Mar-2014 11:39

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.

Posted by gus on 10-Mar-2014 11:48

hi paul. i look forward to seeing your mathematics.

This thread is closed