Data Downloaded with Microsoft Query Not Ported into MS Excel

Problem: Users occasionally report an error bringing data into Microsoft Excel that has been downloaded with the MS Query Add-in (Xlquery.xla) from an external data source. Errors reported include rows of data missing or column headings appearing with no data in the spreadsheet.

Cause: Excel uses an add-in Xlquery.xla, which uses dynamic data exchange (DDE) with MS Query to get the external data. If the downloaded data is visible in MS Query, the download has been successful. This ends the involvement of the Client Access ODBC driver in the download. If you are not receiving the data in Query, Support Line can assist you in debugging the problem. If there are problems bringing the data into Excel from Query, there is a problem with the DDE link. For these types of problems, contact Microsoft directly, or pursue further assistance through a consulting agreement.

Refer to online help for Excel that discusses other options for retrieving external data via an Excel macro. One involves using Data Access Objects (DAO) (Excel 7.0 only), and the other involves using an ODBC add-in called Xlodbc.xla. These options use ODBC directly rather than relying on the DDE link with MS Query. These options are also discussed in Microsoft technical support documents Q141227 and Q124218, which are available on the Microsoft web site.

Note: Microsoft may move or retire documents; therefore, contact Microsoft regarding any availability issues.

Microsoft Query Version 2000 Message SQL0104, Token '$' Not Valid

Problem: Microsoft Query version 2000 (in Office 2000) may fail with a message SQL0104, token '$' not valid.

Cause: MS Query is aliasing one of the fields in the query with the invalid identifier, '$' (with the single quotes). For example, "SELECT MYFLD1, MYFLD AS '$', MYFLD3 FROM MYRDB.MYLIB.MYFILE MYFILE". The single quotes around the $ character identify this as a literal value rather than a column identifier or alias.

Resolution: This is a defect in the MS Query 2000 product. Contact Microsoft.

Microsoft Excel Versions 7 & 8 Generate Invalid Scale Errors

Problem: A query is built in Microsoft Query using a parameter marker for a numeric column. The query runs in Microsoft Query, but when the data is returned to Microsoft Excel, the query is run again and it fails with the error message DIAG[S1094] [IBM] [Client Access ODBC Driver (32-Bit)] Invalid scale value.

Cause: Microsoft Excel is incorrectly binding the parameter marker for numeric columns. It is setting the scale (number of digits in the number) to what appears to be a very large random number. This is a defect in the Microsoft Excel product.

Resolution: The problem was resolved in the Office 2000 suite (Excel version 9).

Microsoft Query Version 2.0 Generates Column Name Length Errors

Problem: The query builder in Microsoft Query generates column name length errors when building a query on an operating system table. SQL0107 is returned by the IBM System i system.

Cause: Query is incorrectly quoting the table name when it builds a select statement. This error is returned:

SQL0107 - Tablename.Columnname too long. Maximum 10 characters.

Microsoft Query generated incorrect ODBC syntax for the statement.

Microsoft Query is not handling delimited identifiers correctly. ANSI IBM® SQL/400® and ODBC define support for delimited identifiers. By definition, a delimited identifier is case sensitive (where ordinary identifiers are converted to uppercase). Our ODBC driver supports delimited identifiers and reports this by responding to SQLGetInfo with the following information:

"SQL_IDENTIFIER_CASE: SQL_IC_SENSITIVE
SQL_IDENTIFIER_QUOTE_CHAR: "

Microsoft Query is incorrectly using this information. To verify, take an SQL log of the failure. MS Query generates queries similar to this:

SELECT FILE.FIELD1, FILE.FIELD2 FROM "SYSTEM.LIBRARY".FILE FILE

This SQL syntax is not valid because SYSTEM.LIBRARY must be treated as one delimited identifier (in this case, the owner) rather than qualifier qualifier-separator owner-name.table-identifier. Some correct examples include:

"SYSTEM"."LIBRARY".TABLE (SYSTEM.LIBRARY.TABLE)
SYSTEM.LIBRARY.TABLE (SYSTEM.LIBRARY.TABLE)
system.library.table (SYSTEM.LIBRARY.TABLE)
"SYSTEM"."LIBRARY"."lctable" (SYSTEM.LIBRARY.lctable)

Note: lctable is interpreted as a case-sensitive, lowercase name because of the delimiter.

Work-around: We are not aware of a fix from Microsoft so we have added a special circumvention with Service Pack SF34548 for V3R1M0 and Service Pack SF35403 for V3R1M1. Our ODBC driver detects that it has been loaded by MSQRY32.EXE. When this condition is detected, support for delimited identifiers is disabled. This allows MS Query to run against most OS/400 or i5/OS tables; however, you cannot run against SQL tables created with case-sensitive names.

Note: MS Query 8.0 seems to have fixed the bug discussed above; however, the Client Access query fix is still active. The fix may be removed in the next release of Client Access (the release after R313).

Microsoft Excel Version 7.0 Generates Column Name Length Errors

Problem: See the section for Microsoft Query version 2.0 above. Excel 7.0 uses Query 2.0 to get external data, so the same problem applies.

MS Query 97 Uses Only File DSNs

Problem: MS Query 97 and MS Excel 97 fail with the error [IBM][Client Access ODBC Driver (32-bit)]General Error.
Cause: According to Microsoft, MS Query 97 uses only file DSNs. Verify that you have properly configured a file Data Source using ODBC Administration and use this data source in query. Microsoft has two technical support documents (Q173521 and Q159557) that explain the differences between types of DSNs (data sources) and also gives a way to point a new file DSN at an existing system DSN so that Query can use the original system DSN. IBM does not verify these support documents are still available.


New Data Source Cannot Be Created Within Microsoft Query 8, 2000 and newer.

Problem: This is a problem which occurs with the Express Client when the connection option for the system has been set to always prompt for a user ID and password. If you then attempt to create a new DSN from within Microsoft Query, it will fail with one of the following error messages depending on the Express service pack applied:

'Communication link failure RC=0015 or RC=8015, CWBSY1006 - User ID is invalid'
or
'Communications link failure. Comm RC=4 - CWB0999 - Unexpected error: unexpected return code 4'
or
'CWBSY0271 'The user profile has no password associated with it'


Cause: What is happening is that Microsoft Query is making two connections. The first time they connect they use the SQL_DriverConnect function setting the connectOption parameter to SQL_DRIVER_PROMPT allowing the ODBC driver to prompt the user for a valid user ID and password. They then make a second connection using the output connection string from the first connection as the input connection string on the second SQL_DriverConnect call. On this second call, the connectOption parameter is set to SQL_DRIVER_NOPROMPT, which does not allow our driver to prompt for a user ID or password. The Client Access ODBC driver does not provide the UID or PWD parameters in the output connection string so this second connection will fail if the user ID and password cache are not available (any time the connection is to prompt every time).

Resolution: The choice to not supply the user ID and password on the output connection string is an intentional design choice to prevent putting the user ID and password into logs and traces. Therefore, this is a permanent design restriction in this environment. A user must create the DSN outside of Microsoft Query using the ODBC Administrator or set the connection option to one of the options other than prompting every time.