PRB: SELECT DISTINCT Fails with Microsoft ODBC for Oracle

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

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.

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: There is no workaround for this behavior with the Microsoft ODBC for Oracle driver.

Steps to Reproduce Behavior

1. Create a table to test with:

   CREATE TABLE FOO (C1 CHAR(20))
   						

2. Insert some data into the table:

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

3. 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.
4. Execute the following SQL statement:

   SELECT DISTINCT * FROM FOO
   						

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