PRB: SQL Server ODBC Driver with ADO Spawns Second Connection

ID: Q191085

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2
Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0

SYMPTOMS

The first row-returning query that you run on an ADO connection to SQL Server, 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:


   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=sa;PWD=;"

   strSQL = "SELECT * FROM Authors"

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

Additional query words:

Keywords : kbADO kbDatabase kbMDAC kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:1.5,2.0,2.1 SP2,5.0,6.0
Platform : WINDOWS
Issue type : kbprb