I'm having some issues while connecting from OpenEdge ODBC and Power BI.

The issue isn't in the connection, but on SQL parameters.

What I have tried so far:

• Connecting without SQL Parameters, but I'm having the 'COLUMN at TABLE has value exceeding its max length or precision.
  
  
• Connecting with this SQL: SELECT ta1, ta2, ..., tan FROM PUB.Table but this is causing the 'Expression.Error: The key didn't match any rows in the table. '.
  
  
• Searching the forums, I've find to try the statement OpenQuery - So SELECT * FROM OpenQuery(MyDB, 'SELECT ta1, ta2, ..., tan FROM PUB.Table') or SELECT * FROM OpenQuery(MyDB, 'SELECT "ta1", "ta2", ..., "tan" FROM PUB.Table'), this is causing 'Table/view/synonynm "SYSPROGRESS.OPENQUERY" cannot be found. (15814)'.
  (Tried to insert PUB on a few places in the query, but didn't work either)
  
  
• Another attempt - SELECT (SELECT ta1, ta2, ..., tan FROM Pub.Table) as MAIN is causing 'Too many values specified(7531)'
  
  The idea to use the SQL is to remove the table that is exceeding the max length, because it's not required to the Power BI's graph.

Any sugestions? Thanks!

CodePudding user response：

This error:

COLUMN at TABLE has value exceeding its max length or precision.

Is a classic OpenEdge DB issue. OpenEdge doesn't enforce length limits on data. All data fields are variable width. There is a "sql width" attribute that defaults 50% wider than the "display format" and this is what your SQL connection uses. That usually works pretty well but some applications have a very flexible attitude about what data they will permit and your SQL queries end up complaining.

There are several ways to fix it. The old school method is to run "dbtool" and select the options to "fix sql width". You can do this manually as-needed or you can run it as a batch job every now and then (maybe nightly, maybe on weekends).

Manually it looks something like this:

If you are running an Openedge release later than 11.4 you can handle these problems automatically via a couple of database startup parameters:

-SQLTruncateTooLarge output
-SQLWidthUpdate on

The first will simply truncate data to the defined width. You have an option to do that just for the results (that is what "output" does) or also for evaluating a query ("on" or "all"). You probably don't really want to truncate fields being used in a WHERE but maybe you do. So you should think about that.

The second option will quietly change the "sql width" when it finds a field that is too wide. If you enable this then SQLTruncateTooLarge will be automatically enabled as well.