FIX: Incorrect Results Using Distinct Keyword with Recordset DTC

ID: Q191971

The information in this article applies to:

SYMPTOMS

When supplying a SQL statement to the Recordset DTC that uses the DISTINCT keyword, the resulting recordset contains the wrong number of records in the resultset. Duplicate records should be ignored as specified by the DISTINCT keyword. However, duplicate records are being returned.


RESOLUTION

To work around this behavior, set the cursor location to be server-side:

  1. Open the Properties page of the Recordset DTC.


  2. Click the Advanced tab.


  3. Set the Cursor Location property to "2 - Use server-side cursors."



STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

This bug was corrected in Visual Studio 6.0 Service Pack 3.

For more information about Visual Studio service packs, please see the following articles in the Microsoft Knowledge Base:

Q194022 INFO: Visual Studio 6.0 Service Packs, What, Where, Why

Q194295 HOWTO: Tell That Visual Studio 6.0 Service Packs Are Installed


MORE INFORMATION

Steps to Reproduce Behavior

The following steps require a data connection to SQL Server's PUBS database:
  1. Create a new Active Server Pages (ASP) page.


  2. Drag a Recordset DTC from the Design-Time Controls toolbox onto the page below the <BODY> tag. Right-click it, and open its Property page.


  3. In the General tab, set its Connection property to the PUBS data connection.


  4. Set the radio button in the "Source of Data" group box to the SQL Statement option.


  5. In the area below the SQL Statement button, type the following:


    6. Select DISTINCT title_id from titleauthor
  6. Close the Property page.


  7. Drag a grid to the page.


  8. Right-click and open its Property page. On the Data tab, set the Recordset property to the recordset on the page (usually Recordset1). In the Available Fields, choose the title_id field.


  9. Click OK to close the Property page.


  10. Save the page and preview it in the browser.


A grid will appear and contain 25 total records across two pages. (The default records per page is 20 for the grid. The grid should show 20 records on the first page, and 5 on the next page).

However, when running the same query in SQL Server's ISQL_w, or by changing the Recordset DTC's curor location to "server-side" the following correct results are returned (this is from ISQL_w): 

   title_id
   --------
   BU1032
   BU1111
   BU2075
   BU7832
   MC2222
   MC3021
   PC1035
   PC8888
   PC9999
   PS1372
   PS2091
   PS2106
   PS3333
   PS7777
   TC3218
   TC4203
   TC7777

   (17 row(s) affected)

Additional query words: 


Keywords          : kbADO kbCtrl kbVisID600bug kbGrpASP kbSQLServ650bug kbVS600sp3fix 
Version           : WINDOWS:6.0; winnt:6.5
Platform          : WINDOWS winnt 
Issue type        : kbbug

Last Reviewed: May 24, 1999