Microsoft KB Archive/191085

= PRB: SQL Server ODBC Driver with ADO Spawns Second Connection =

Article ID: 191085

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q191085



SYMPTOMS
The first row-returning query that you run on an ADO connection to SQL Server 6.5 using the SQL Server ODBC driver causes the following query to be run on SQL Server: select USER_NAME select usertype,type,name from systypes where usertype<=100



CAUSE
The OLE DB provider for ODBC drivers requests information from SQL Server to understand how to manage queries and results.



STATUS
This behavior is by design.



MORE INFORMATION
If your initial row-returning query uses a client-side recordset or a server-side firehose cursor, this behavior results in a temporary connection to your SQL Server which will be closed as soon as the query has completed.

Steps to Reproduce Behavior
Use SQL Server's SQL Trace utility to see that the following code establishes a second connection to the database. Modify the connection string to connect to your SQL Server database as follows:

Note You must change UID= and PWD= to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database. Dim cnPubs As New ADODB.Connection Dim rsAuthors As New ADODB.Recordset Dim strConn As String, strSQL As String

strConn = "Provider=MSDASQL;Driver={SQL Server};" & _

"Server=MyServer;Database=pubs;UID= ;PWD= ;"

strSQL = "SELECT * FROM Authors"

cnPubs.CursorLocation = adUseClient cnPubs.Open strConn rsAuthors.Open strSQL, cnPubs, adOpenStatic, adLockReadOnly, adCmdText

Keywords: kbdatabase kbprb kbmdacnosweep KB191085

-

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

© Microsoft Corporation. All rights reserved.