OLE DB Provider/ODBC Driver for DB2 May Break Access/Jet Applications (244262)


The information in this article applies to:

  • Microsoft SNA Server 4.0 SP2
  • Microsoft SNA Server 4.0 SP3
  • Microsoft ODBC Driver for DB2 4.0 SP3
  • Microsoft OLE DB Provider for DB2 4.0 SP3
This article was previously published under Q244262

SYMPTOMS

Some DB2 platforms (listed in the "More Information" section of this article) do not allow sorting by a column that is not part of the Select list. For example, the following query fails with a DB2 specific -208 error:

SELECT COL1 FROM TABLE ORDER BY COL2

The Microsoft OLE DB Provider for DB2 incorrectly returns VARIANT_FALSE for the property DBPROP_ORDERBYCOLUMNSINSELECT, and the Microsoft ODBC Driver for DB2 incorrectly returns "N" on the SQLGetInfo(SQL_ORDER_BY_COLUMNS_IN_SELECT) call.

The above response from the provider or driver causes the Microsoft Jet database engine to assume that DB2 does not require the order by columns to be part of the Select list, and consequently, the Jet database engine generates a query that is rejected by the DB2, which breaks the application with the provider and the driver returning the following error message:
A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE:42707, SQLCODE: -208

RESOLUTION

To resolve this problem, obtain the latest service pack for SNA Server 4.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

215838 How to Obtain the Latest SNA Server Version 4.0 Service Pack


STATUS

Microsoft has confirmed that this is a problem in Microsoft SNA Server version 4.0 Service Pack 2 and Service Pack 3.

This problem was first corrected in SNA Server 4.0 Service Pack 4.

MORE INFORMATION

The following platforms have been tested and found to have the requirement for order by columns to be part of the select list:

DB2 for MVS V4R1 and higherDB2 for OS/390 V5R1 and higherDB2 for OS/400 V3R2 and higher

Note: The only supported platform that was not tested for this requirement was DB2 for OS/390 V6R1.

For the following platforms, this is not a requirement; therefore, they are not affected by this problem:

DB2 UDB for Windows NT V6R1DB2 UDB for AIX V5R2

Note: There may be other platforms or versions of DB2 that are either affected or not affected by this problem. This may be found by running a query against them.

When this fix is applied, the provider or driver returns the correct information to Jet, which then generates a query that's acceptable to DB2. If the error still persists after applying this fix, it could be due to a bug with the Jet engine (fixed in the Jet3.5 SP3 update). For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

235960 ACC97: ORDER BY Clause Is Not Valid... Error with Linked Table

Modification Type:MinorLast Reviewed:2/12/2004
Keywords:kbbug kbfix kbQFE kbSNA400PreSP4fix kbSNA400sp4fix KB244262
©2004 Microsoft Corporation. All rights reserved.