Prevent an implicit connection when ADO connects to SQL Server

Ado|server prevents an implicit connection when ADO connects to SQL Server
Date:2002/9

Prepared by: Zheng

Article last modified on 2002-9

The information in this article applies to:

Ümicrosoft SQL Server 2000,7.0

Ümicrosoft ADO 2.5
Problem Statement:
Database server: Microsoft SQL Server 2000 and 7.0;

Database server patches: Microsoft SQL server ServicePack1;

ADO Name: Microsoft data access-activex Data Objects 2.5 Type Library

ADO version: 2.61.7326.0



When we execute the following VB code, our developers have a question:

Cnn. Open "Provider=SQLOLEDB.1;

Persist Security Info=false; User Id=sa;

Initial Catalog=freemail;data Source=svr; Connectiontimeout=10 "," "," ",-1
sql = "SELECT * from Users"

Set rs = cnn. Execute (SQL)
Set Rs2 = CNN. Execute (SQL)
Set Rs3 = CNN. Execute (SQL)

When you execute this code, you see in SQL Server Profiler that there is a audit login event before each SQL statement executes. The audit login event is interpreted as: "Collects all new connection events that occurred since the trace was started, such as client requests to connect to the server running Microsoft®sql Server™ instance." That is, once you connect SQL Server with a connection object, you will still re-establish the connection each time you execute the SQL statement, using the same connection?!

To establish the connection's profiler record (in chronological order):



EventClass

Text Data

Tracestart



Audit Login

(First time connection)

--Network PROTOCOL:LPC

SET QUOTED_IDENTIFIER ON

Set Implicit_transactions off

Set CURSOR_CLOSE_ON_COMMIT off

Set ANSI_WARNINGS on

Set ANSI_PADDING on

Set ANSI_NULLS on

Set CONCAT_NULL_YIELDS_NULL on

Set Language Simplified Chinese

Set DateFormat YMD

Set Datefirst 7

Sql:stm tstarting

Select * from users

Audit Login

(2nd time Connection)

--Network PROTOCOL:LPC

SET QUOTED_IDENTIFIER ON

Set Implicit_transactions off ... Slightly

Sql:stm tstarting

Select * from users

Audit Login

(3rd time Connection)

--Network PROTOCOL:LPC

SET QUOTED_IDENTIFIER ON

Set Implicit_transactions off ... Slightly

Sql:stm tstarting

Select * from users

Audit Logout



Audit Logout



Audit Logout



Tracestop





And if every sentence on CNN. After execute, plus rs.close (), there will be no audit login events before each execute, but a contiguous 3 sql:stmtstarting events.

Does this often create physical connections that affect performance? As usual, you should reuse the same connection.
Cause:
This is known as an implicit login.

When a set Ado.recordset object receives the recordset returned by ADO.Connection.Execute, an implicit login occurs, a physical connection is established with the database server, and the connection is not reusable or pooled.

The reason for this is:

Because the SQL Server OLE DB provider doesn ' t permit more than one set of results to is pending on a connection where the Results are being returned by means of a forward-only, read-only (default-resultset) cursor, the provider needs to create An additional SQL Server connection to execute a second command on the connection. The provider'll only does this implicitly if the Data Source dbprop_multipleconnections be set to VARIANT_TRUE.



Refer to Microsoft's KB documentation:

http://support.microsoft.com/default.aspx?scid=kb; En-gb;q271128&gssnb=1

"Prb:implicit Connections Created by" SQL Server OLE DB Provider (SQLOLEDB) Are not pooled


"Do not repeat code snippets for database connections":

Avoid implicit login by changing the properties of Ado.recordset
Dim cn as New ADODB. Connection
Dim rs as New ADODB. Recordset
Dim rs2 as New ADODB. Recordset
Cn.open .....



Rs. CursorType = adOpenStatic

Rs. ActiveConnection = cn
Rs. Open "SELECT * FROM Orders"

Rs. CursorType = adOpenStatic
Rs2. ActiveConnection = cn
Rs2. Open "SELECT * FROM Orders"

It seems, indeed, as Microsoft has said, that an implicit connection occurs only when a default recordset is received. This problem does not occur if you set Ado.recordset to another type, such as a static set.

Of course, the default Recordset's properties Forward-only, read-only the fastest.



Writen by zhengyun@tomosoft.com



The information contained in this document represents the current view of Zhengyun on the issues discussed at the date of release, Zhengyun does not guarantee the accuracy of the information given after the date of release.
This document is for informational purposes only. Zhengyun does not make any express or implied warranties with respect to the information in this document.