PRB: SQL Server ODBC Driver with ADO Spawns Second Connection

   select USER_NAME() select usertype,type,name from systypes where
   usertype<=100
				

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=<username> and PWD=<strong password> 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=<username>;PWD=<strong password>;"

   strSQL = "SELECT * FROM Authors"

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