Microsoft KB Archive/277667

= PRB: SELECT DISTINCT Fails with Microsoft ODBC for Oracle =

Article ID: 277667

Article Last Modified on 12/5/2003

-

APPLIES TO


 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.0
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q277667



SYMPTOMS
When a SELECT DISTINCT statement is used with the Microsoft ODBC for Oracle driver, the following error may occur:

DIAG [HYC00] [Microsoft][ODBC driver for Oracle]Cannot use Keyset-driven cursor on join, with distinct clause, union, intersect or minus or on read only result set (0)



CAUSE
The Microsoft ODBC for Oracle driver uses the hidden Oracle column ROWID, which assigns a unique ID for each row. Because this negates the uniqueness constraint imposed by the DISTINCT keyword, the driver issues the above error rather than bring back multiple copies of the same data.



RESOLUTION
If you require the use of the DISTINCT keyword, you can use the Microsoft OLE DB Provider for Oracle, because the provider doesn't use the ROWID column when DISTINCT is used. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

258997 INFO: Oracle OLE DB Provider and ROWID Use

There is no workaround for this behavior with the Microsoft ODBC for Oracle driver.



Steps to Reproduce Behavior
 Create a table to test with:

CREATE TABLE FOO (C1 CHAR(20))

 Insert some data into the table:

INSERT INTO FOO VALUES('aaa') INSERT INTO FOO VALUES('bbb')

 Using the ODBC API SQLSetConnectAttr, set the connection attribute SQL_CURSOR_TYPE to the value SQL_CURSOR_KEYSET_DRIVEN. This ensures that you get a keyset cursor, which uses the hidden ROWID column. Execute the following SQL statement:

SELECT DISTINCT * FROM FOO

</li></ol>

The ODBC call returns SQL_ERROR, and the associated error text will be the error shown in the &quot;Symptoms&quot; section of this article.

Keywords: kboracle kbprb KB277667

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.