[Stackoverflow]

I'm working with a Progress 9.1E database application. (Yes, I'm aware of how bad that sounds).

My problem began when I ran SELECT * FROM SYSPROGRESS.SYSCOLUMNS_FULL, ...which gave me this error:

ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]Column _Format in table PUB._Field has value exceeding its max length or precision.
​

(For those unaware, the SYSCOLUMNS_FULL table is actually a SQL VIEW that's defined in PUB._Sysviews, and it's defined as (essentially) SELECT ... FROM PUB._Field INNER JOIN PUB._File)

Now if this was a normal user table then the solution is to edit the SQL Width of that column in the Data Dictionary tool - but the problem here is that _Field is a built-in metaschema table, and the Data Dictionary tool does not allow editing of the SQL Width of its columns.

...but I figured out that you can Unfreeze the table, then edit it, then re-Freeze it, which is what I did: I changed _Format's SQL Width to 1024 chars:



...and disregarded the dire warning...



...and I fully restarted the machine (as the _mprosrv.exe and _sqlsrv2.exe processes keep the schema cached indefinitely, I understand).

...and it did not fix the problem.


So my next was to run DBTool's "SQL Width & Date Scan w/Report Option" and "SQL Width Scan w/Fix Option" modes; which reported finding zero SQL Width errors (and only a single Date error).


So my next step was to try to see what's in PUB._Field that could cause this; so I ran this query:

SELECT
t."_File-Name" AS Tbl,
f."_Field-Name" AS Col,
f."_Data-Type" AS Typ,
f."_Format" AS Fmt,
f."_Width" AS Wid

FROM
PUB."_Field" AS f
INNER JOIN PUB."_File" AS t ON f."_File-recid" = t.ROWID


...which gave me the same error as above. Which makes sense: clearly I can't access or use the "_Format" directly without causing that error, so I tried the trick where you use the ODBC scalar-function escape syntax, which should prevent "bad values" from being exposed to the ODBC layer:

SELECT
{ fn LENGTH( ""_Format"" ) } AS len
FROM
PUB.""_Field""


...and this did not fix the problem.


So I used the Data Administration tool to dump the _Field metaschema table/view to a .d file - as well as Exporting it to CSV so I could open it in Excel.

Here's the Text dump opened in Excel, with all rows sorted (descending) by the length of their _Format column value:



...and the longest defined _Format value is 65 characters long.

...so perhaps the problem isn't _Format at all, but is some bug somewhere else that merely presents itself this way? Either way, this is beyond my current abilities.


So, I'm stumped - and because this version is so out-of-date there's no option for professional support.

Continue reading...