Microsoft KB Archive/181479

= FIX: Getting SELECT DISTINCT to Work Under ADO with SQL Server =

Article ID: 181479

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 6.5 Service Pack 1
 * Microsoft SQL Server 6.5 Service Pack 2
 * Microsoft SQL Server 6.5 Service Pack 3
 * Microsoft SQL Server 6.5 Service Pack 4

-



This article was previously published under Q181479



SYMPTOMS
Under certain conditions with ActiveX Data Objects (ADO), SELECT DISTINCT in your SQL statement will not provide distinct records.

Any of the following situations produces incorrect results with SQL Server:
 * You open a recordset with rs.open "select ....." with no command object.
 * You open a recordset with the rs.open command.
 * You open a recordset with Set rs= command.execute and the cursorlocation was set to rduseclient. This happens either when you use the Recordset.Open method or when the recordset is obtained from a command object by setting the recordset equal to the return value of Command.Execute.



CAUSE
The reason this does not work with adUseClient is that ADO uses SQL Server's FOR BROWSE option on the SQL statement to get extended metadata in order to process updates. The use of this option causes SQL version 6.5 to ignore the DISTINCT keyword.



RESOLUTION
Here are the two possible techniques you can use to get SELECT DISTINCT to work as expected:
 * Set the CursorLocation to adUseServer instead of adUseClient.
 * When using the Open method with adUseClient you can work around this problem by using a command object and setting its ActiveConnection to the connection used in the Open statement. The "Steps to Reproduce Behavior" section demonstrates the workarounds for both the Open and the Command methods.



STATUS
This problem is fixed with the release of Service Pack 5 for SQL Server 6.5 and SQL Server 7.x.



Steps to Reproduce Behavior
 Create a standard .exe project, and from the Project menu, choose References, then select Microsoft ActiveX Data Objects 1.5 Library.  Add 2 command buttons to the form and paste the following code in the Declaration section of the form:

Note You must change Username= and PWD= to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database. Option Explicit

Dim conn As New Connection 'Dim com As New Command 'Uncomment to make DISTINCT work. Dim rs As New Recordset

Private Sub Command1_Click

conn.open "mymachine", " ", " " conn.cursorlocation = adUseClient 'Change to aduseServer. 'com.ActiveConnection = conn 'Uncomment to make DISTINCT work.

rs.open "select distinct state from authors", conn, _ adOpenKeyset, adLockOptimistic MsgBox rs.RecordCount End Sub

Private Sub Command2_Click Dim counter As Integer counter = 0 conn.Open "dsn=mymachine;Username= ;PWD= ;" com.ActiveConnection = conn conn.CursorLocation = adUseClient  'Comment this 'conn.CursorLocation = adUseServer 'and uncomment this. com.CommandText = "select distinct state from authors" com.CommandType = adCmdText rs.CursorType = adOpenKeyset 'cursortype will be adopenforwardonly with adUseServer. Set rs = com.Execute While Not rs.EOF Debug.Print rs!State counter = counter + 1 rs.MoveNext Wend Debug.Print "" MsgBox counter 'recordcount return -1 with adUseServer End Sub  Run the code and click Command1. Note the number of records returned. Change adUseClient to adUseServer in the CursorLocation. Run the code again and fewer records are returned if there are duplicate states. Change aduseServer back to adUseClient. Uncomment the Dim of the command object and the code in Command1. Run the code again. You receive the correct number of records using this technique also.NOTE: If you set the recordset properties ahead of time and use the syntax Rs.open com the recordcount returned will be incorrect. However, to get the correct recordcount using the Rs.Open method you must use a command object and set its active connection.

 Run the code again and click Command2. Note the counter. Comment the CursorLocation for adUseClient and uncomment the adUseServer CursorLocation. Run the code and note the counter again.</li></ol>

Keywords: kbbug kbfix kbsqlprog kbdatabase kbado210sp2fix KB181479

-

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

© Microsoft Corporation. All rights reserved.