FIX: Incorrect Results Using Distinct Keyword with Recordset DTC

View products that this article applies to.

This article was previously published under Q191971

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.

Back to the top

RESOLUTION

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

Open the Properties page of the Recordset DTC.
Click the Advanced tab.
Set the Cursor Location property to "2 - Use server-side cursors."

Back to the top

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are 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:

194022 (http://support.microsoft.com/kb/194022/EN-US/ ) INFO: Visual Studio 6.0 Service Packs, What, Where, Why

194295 (http://support.microsoft.com/kb/194295/EN-US/ ) HOWTO: Tell That Visual Studio 6.0 Service Packs Are Installed

Back to the top

MORE INFORMATION

Steps to Reproduce Behavior

The following steps require a data connection to SQL Server's PUBS database:

Create a new Active Server Pages (ASP) page.
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.
In the General tab, set its Connection property to the PUBS data connection.
Set the radio button in the "Source of Data" group box to the SQL Statement option.
In the area below the SQL Statement button, type the following:
Select DISTINCT title_id from titleauthor
Close the Property page.
Drag a grid to the page.
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.
Click OK to close the Property page.
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)

Back to the top