Export from progress to SQL server Database - Error messages

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.

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

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.

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.

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

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

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! ;)

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

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

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.

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.

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

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

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

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