Odd Error using ODBC to IBM U2

Monday 25 April 2011 @ 8:42 pm

Here is an odd case I helped (partially) resolve recently.   The customer is using CR v11(XI) to connect to an IBM U2 database (via ODBC).  The database was hosted on a Windows 2003 server.

They had two seemingly identical client system configurations using the same exact version of CR and the same ODBC driver.  On both we would start a simple report using  just one table and a few fields.  The report would run fine in one environment but the other would generate an error message:

Failed to retrieve data from the database.
Details: HY000:[IBM][UVODBC][1401032]Error ID: 46 Severity:ERROR Facility: DBCAPERR - UCI Error. Func: SQLPrepare(DML); State: S1000; uniVerse code: 950088; Msg: [IBM][SQL Client][UNIVERSE]Universe/SQL:syntax error. Unexpected filename.

When I compared the two different SQL Queries I noticed that the query that worked added a ‘1’ to the table alias.  The one that didn’t work did NOT add the ‘1’ but used the table name as the alias.  I checked the Database Expert of the report that worked and the alias change wasn’t coming from the rpt file, so it wasn’t clear which layer was adding the alias change into the SQL.   To confirm that the alias was the problem I went into the Database Expert of the report that did not work, changed the table alias within CR (adding a ‘1’ to the end) and then the report ran correctly.  And best of all, the report with the manually changed alias worked in both environments.  The report whose alias was automatically changed would only run in the environment where it was created.

So we had a working solution but two questions remained:

1) How did the original working environment know to automatically change the alias?  I have suggested that they use “modules” to compare the systems to see if any installed files are different.  Other than that there may be an ODBC option somewhere that makes this happen.

2) Why does the database consider the actual table name to be an ‘Unexpected filename’.  In most databases the default alias for a table is the table name, but that doesn’t seem to work here.

Several Internet searches related to this turned up others getting the same error, but none that I found even mentioned the alias adjustment – and no one suggested a solution.  I will post more if we learn anything interesting.

Leave a Reply

Recrystallize Pro