ODBC Driver for DB2 may cause "Index Already Exists" error (811963)


The information in this article applies to:

  • Microsoft Host Integration Server 2000
  • Microsoft Host Integration Server 2000 SP1

SYMPTOMS

When you use the Microsoft ODBC Driver for DB2 to link a table from Microsoft Access, you may receive an "Index Already Exists" error.

CAUSE

Microsoft Access calls the SQLStatistics ODBC API function to retrieve all the indexes that exist in a table. When the SQLStatistics API is called, the ODBC Driver issues an SQL statement to the backend DB2 database that queries the SYSINDEX table in SYSIBM. This query must return all the information from the indexes according to some specific order, such as NON_UNIQUE, TYPE, INDEX_QUALIFIER, and INDEX_NAME. The ODBC Driver for DB2 does not sort the returned result set. This results in the "Index Already Exists" error.

RESOLUTION

Service pack information

To resolve this problem, obtain the latest service pack for Microsoft Host Integration Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

328152 How to obtain the latest service pack for Host Integration Server 2000

Hotfix information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.
   Date         Time   Version      Size     File name
   ------------------------------------------------------
   16-Jan-2003  12:08  5.0.0.900    880,912  Mseidb2d.dll

Note Because of file dependencies, the most recent fix that contains the preceding files may also contain additional files.

WORKAROUND

The problem can be avoided by using an SQL pass-through query instead of linking the tables in Microsoft Access.

For more information about how to create an SQL pass-through query, click the following article number to view the article in the Microsoft Knowledge Base:

303968 How to create an SQL pass-through query in Access

STATUS

Microsoft has confirmed that this is a problem in Microsoft Host Integration Server 2000 and Host Integration Server 2000 SP1.

This problem was corrected in Microsoft Host Integration Server 2000 Service Pack 2.

MORE INFORMATION

The updated ODBC Driver for DB2 uses an "ORDER BY" clause to sort the indexes that are returned by the SYSINDEX table query.
Modification Type:MajorLast Reviewed:7/14/2005
Keywords:kbQFE kbfix kbbug KB811963 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.