Column Key in table has value exceeding its max length or precision

[hambo12]

Bit of background info first:

I have to connect to a Progress 9.1e database, via SQL reporting services.
I connect via ODBC, and attempt to run a query on a table:

select * from pub.sermaster

This returns the following error:

[DataDirect][ODBC PROGRESS driver][PROGRESS]Column Key in table PUB.sermaster has value exceeding its max length or precision.".

I have been doing a lot of research, and it seems this is due to Progress' relaxed restrictions on column size. As far as I can tell, there is a way to fix this, using the DBTOOL in Progress.

This is a huge risk for us, as the database and application is hosted via a third party, and they are hesitant to make any changes to their databases.

I can see I have two options:

1. Somehow convince the progress guys to run the dbtool on a regular basis (last resort, I dont know how this will affect our tables or data).
2. Automate a csv dump from progress, then import daily into an SQL database. Use SQL reporting services to connect to the SQL DB and run reports.

However, I am looking for opinions on what is the best way to go about this. I am quickly getting fed-up with Progress databases!!

 

[tamhas]

I'm not sure why you are fed up with Progress since it seems to me that the stupid behavior here is SQL blowing up. Many people seem to think that setting a default format in a Progress database should somehow define a maximum field width as is the case in many SQL databases ... but why exactly is that a good thing. If the data is wider than that, then the data is wider and you would lose something if you truncated it! That default format is just that, a default. It can easily be overridden in the UI and it is common, in fact, for people to have something like an "X(60)" format on a field that can get a lot of data so that they can write simple queries with one line output which will show the beginning part of the field, even though an editor widget is needed to see the full information.

The answer is just to run dbtool periodically. It is easy and quick and will solve the problem. Chances are that the first time you run it, it will make a bunch of changes, but then on second and following runs it will make few or no changes because the SQL width has already been boosted high enough.

 

[TomBascom]

I'm not sure why you are fed up with Progress since it seems to me that the stupid behavior here is SQL blowing up...

My thoughts exactly!

The vendor's hesitation to run dbtool is unwarranted. I may be getting my name and picture added to another dartboard but my guess is that they are probably fairly inexperienced at running production systems. It is the Achille's heel of vendors -- whether it is a SaaS offering or their support desk giving you advice on your systems they just don't have experience with these things.

 

[hambo12]

My thoughts exactly!

The vendor's hesitation to run dbtool is unwarranted. I may be getting my name and picture added to another dartboard but my guess is that they are probably fairly inexperienced at running production systems. It is the Achille's heel of vendors -- whether it is a SaaS offering or their support desk giving you advice on your systems they just don't have experience with these things.

Thanks all for the responses. The vendor has suggested we bring down the database, go into Data dictionary, and lengthen the SQL width field. Apparently that will fix the issue.

Can anyone see any issues with this?

Also, maybe I was a bit hasty, I should have said I am getting fed-up with trying to integrate progress with SQL RS...

 

[TomBascom]

I'd take the dbtool approach.

The downsides to changing the field width are that:

1) you probably don't really know what to change it too.

2) there might be 4gl code that is using the existing value in the dictionary and changing it might break something by, for instance, making a field too wide to fit on the screen.

IMHO dbtool is safe and effective and this is what it was designed for. Use it.

 

[Casper]

dbtool is very fast and can be run online with (SQL) client users connected to the database. Changes will be seen immediately for the connected SQL clients.
Due to the nature of Progress (no fixed field lengths) it will be an unworkable solution to stop the database every time a field exceeds the maximum width.
When you query the database a lot with SQL then you should make dbtool a scheduled job which runs daily.

Casper.