Connection pooling issue with pass through queries

[Bill E.]

I'm creating an MS Access application that connects to a SQL Server
2005 database using pass-through queries and ADO to call stored
procedures. I ran a SQL trace and started clicking through various
forms to look at how things were being handled on the database. I
noticed an alarming number of connections were opened as if there was
absolutely no connection pooling. After several clicks I had 20 new
connections. It appeared that a new connection was being opened by
each pass-through query even though the connection strings are exactly
the same in each (I set them globally in code at application
startup). I also noticed that if I set my connection string to use a
SQL Server login (username/password) rather than a trusted connection,
the problem went away and connections were properly reused. Note that
calls to the database made via ADO did not exhibit this problem.
Also, I'm not using an ODBC DSN.

Is there a reason why I'm seeing this behavior? Is there a setting
somewhere that I've neglected to change?

Bill E.
Hollywood, FL

[Rick Brandt]

Bill E. wrote:

I'm creating an MS Access application that connects to a SQL Server
2005 database using pass-through queries and ADO to call stored
procedures. I ran a SQL trace and started clicking through various
forms to look at how things were being handled on the database. I
noticed an alarming number of connections were opened as if there was
absolutely no connection pooling. After several clicks I had 20 new
connections. It appeared that a new connection was being opened by
each pass-through query even though the connection strings are exactly
the same in each (I set them globally in code at application
startup). I also noticed that if I set my connection string to use a
SQL Server login (username/password) rather than a trusted connection,
the problem went away and connections were properly reused. Note that
calls to the database made via ADO did not exhibit this problem.
Also, I'm not using an ODBC DSN.

Is there a reason why I'm seeing this behavior? Is there a setting
somewhere that I've neglected to change?

Bill E.
Hollywood, FL

Why are you worried about it? Have you had a single problem that you would
have noticed had you NOT used SQL trace?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Bill E.]

Rick,

That's almost like asking why we should bother with database
connection pooling. It's a question of efficient use of resources and
avoiding problems that may occur in production. I was one single user
clicking through one single form and opened over 20 connections.
That's just plain sloppy and unacceptable. Who knows how many
connections would be opened with many users working on a complete
application in extended sessions. It wasn't long ago that I had to
fix someone else's web application because it opened hundreds of
connections and stopped all users in their tracks when the number of
allowable connections was exceeded.

The problem is eliminated when I set the connection string to use a
SQL Server login instead of a trusted connection. Until I understand
what's going on here, I'm going to stick with that. Any insight into
what may be causing this would be welcomed.

Thanks,

Bill E.
Hollywood, FL

[Rick Brandt]

Bill E. wrote:

Rick,

That's almost like asking why we should bother with database
connection pooling. It's a question of efficient use of resources and
avoiding problems that may occur in production. I was one single user
clicking through one single form and opened over 20 connections.
That's just plain sloppy and unacceptable. Who knows how many
connections would be opened with many users working on a complete
application in extended sessions. It wasn't long ago that I had to
fix someone else's web application because it opened hundreds of
connections and stopped all users in their tracks when the number of
allowable connections was exceeded.

The problem is eliminated when I set the connection string to use a
SQL Server login instead of a trusted connection. Until I understand
what's going on here, I'm going to stick with that. Any insight into
what may be causing this would be welcomed.

I can only tell you that I have been using ODBC with trusted connections
with around 200 users for over a decade and have never once had an issue
with it.

Connections ARE re-used.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[lyle fairfield]

In ADPs multiple connections cause problems when application roles are
used as per this quote from http://support.microsoft.com/kb/308312.

"Unlike with other database objects, Access does not always use the
same connection to retrieve the data source of a subform. Access
frequently (but not always) creates a new connection to SQL Server
just to handle the subform recordset, or to retrieve the linking field
data that connects the subform to the main form. Because this new
connection does not have the application role applied, a permissions
error may be generated if you do not have explicit permissions to the
database object. Unfortunately, this means that there is no reliable
way to use bound subforms when application roles are applied. The only
effective workaround is to have completely unbound subforms, with the
data manipulation handled programmaticall y. This is the most serious
limitation when using application roles in Access."

I believe that "Access frequently (but not always) creates a new
connection to SQL Server just to handle the subform recordset" is not
the whole story. In my experience Access frequently (but not always)
creates a new connection to SQL Server to handle any implicit Select,
Insert, Update or Delete procedure, including record-based combo-boxes
and list-boxes. It would be helpful if "not always" could be
documented as to when. I have found that "not always" is
unpredictable, varying from day-to-day and server to server.

I have never used application roles with ODBC and can't comment on
that combination.

A different kind of problem caused by multiple connections is the
effect they may have on the application's credibility. When a DBA/SA
sees one hundred five connections from the application to his server
when she knows only seventeen users have the application open he may
be alarmed. And she may communicate that alarm and concern for
efficiency, merited or not, to the people who are making decisions
about buying Access applications.

On May 31, 10:00*pm, "Rick Brandt" <rickbran...@ho tmail.comwrote:

Why are you worried about it? *Have you had a single problem that you would
have noticed had you NOT used SQL trace?

[Bill E.]

Lyle,

Thanks for the information. I'm not using an ADP and I'm not using
application roles, although I was considering it. I agree that it
would be helpful if it were documented somewhere how Access is
handling connections to SQL Server.

I found something else that was strange. It appeared that pass-
through queries were being called twice for some reason. That is to
say that my trace was showing two calls in a row to the same stored
procedure every time. However, when I ran a pass-through query
directly by clicking on the query and running it, the trace showed
only one call to the proc as expected. I thought perhaps the problem
was due to poor form design on my part. To test this, I created a
form with nothing but a single combobox and bound the combobox to the
pass through query. When I opened the form, I looked at my trace and
voila--two calls to the same proc on SQL Server! Do you have any idea
why this would be happening?

Bill

[lyle fairfield]

"Bill E." <bi********@net scape.netwrote in news:0ebc7349-14be-49e2-b40f-
f7**********@x1 g2000prh.google groups.com:

Lyle,

Thanks for the information. I'm not using an ADP and I'm not using
application roles, although I was considering it. I agree that it
would be helpful if it were documented somewhere how Access is
handling connections to SQL Server.

I found something else that was strange. It appeared that pass-
through queries were being called twice for some reason. That is to
say that my trace was showing two calls in a row to the same stored
procedure every time. However, when I ran a pass-through query
directly by clicking on the query and running it, the trace showed
only one call to the proc as expected. I thought perhaps the problem
was due to poor form design on my part. To test this, I created a
form with nothing but a single combobox and bound the combobox to the
pass through query. When I opened the form, I looked at my trace and
voila--two calls to the same proc on SQL Server! Do you have any idea
why this would be happening?

Bill

Not really! All I can think is that ODBC can translate a JET/VBA
expression, say, TRIM(x), to its T-SQL equivalent RTRIM(LTRIM(x)) [this is
not a good example] and perhaps, to do so it must visit the SQL Server
twice, once to assess and plan, and a second time to do the work. But I
think it doesn't attempt to do that with Pass Through Queries, it just
[passes them through].

[OT] I often create and maintain an independent ADO connection (as below)
when dealing with SQL Server and use it rather than
CurrentProject. Connection where ever possible. Depending on the version of
Access, one can use an ADO recordset for bound forms, and bypass the normal
methods of binding.

Dim mDefaultConnect ion As ADODB.Connectio n

Private Sub OpenConnection( ByRef Connection As ADODB.Connectio n)
Set Connection = New ADODB.Connectio n
With Connection
.CursorLocation = adUseClient
.Provider = "sqloledb.1 "
With .Properties
.Item("Data Source") = "Place_Hold er"
.Item("Initial Catalog") = "Place_Hold er"
-----
.Item("PassWord ") = "Place_Hold er"
.Item("User ID") = "Place_Hold er"
-----
OR
-----
..Item("Integra ted Security") = "SSPI"
-----
End With
.Open
End With
End Sub

Public Function DefaultConnecti on() As ADODB.Connectio n
If mDefaultConnect ion Is Nothing Then _
OpenConnection mDefaultConnect ion
Set DefaultConnecti on = mDefaultConnect ion
End Function

Public Function NewConnection() As ADODB.Connectio n
OpenConnection NewConnection
End Function

[Mary Chipman [MSFT]]

Here are some resources you can try -- It might help to set up ODBC
Trace to troubleshoot from that side. The optimizing paper listed
first explains how Jet works with SQL Server. Make sure to turn off
trace when you're done - it can really chew up resources. HTH.

Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx

Data Access Tracing
http://msdn.microsoft.com/en-us/library/aa964124.aspx

How To Generate an ODBC Trace
http://support.microsoft.com/kb/274551

--Mary

On Sat, 31 May 2008 13:55:06 -0700 (PDT), "Bill E."
<bi********@net scape.netwrote:

>I'm creating an MS Access application that connects to a SQL Server
2005 database using pass-through queries and ADO to call stored
procedures. I ran a SQL trace and started clicking through various
forms to look at how things were being handled on the database. I
noticed an alarming number of connections were opened as if there was
absolutely no connection pooling. After several clicks I had 20 new
connections. It appeared that a new connection was being opened by
each pass-through query even though the connection strings are exactly
the same in each (I set them globally in code at application
startup). I also noticed that if I set my connection string to use a
SQL Server login (username/password) rather than a trusted connection,
the problem went away and connections were properly reused. Note that
calls to the database made via ADO did not exhibit this problem.
Also, I'm not using an ODBC DSN.

Is there a reason why I'm seeing this behavior? Is there a setting
somewhere that I've neglected to change?

Bill E.
Hollywood, FL

[Erland Sommarskog]

Bill E. (bi********@net scape.net) writes:

That's almost like asking why we should bother with database
connection pooling. It's a question of efficient use of resources and
avoiding problems that may occur in production. I was one single user
clicking through one single form and opened over 20 connections.
That's just plain sloppy and unacceptable. Who knows how many
connections would be opened with many users working on a complete
application in extended sessions. It wasn't long ago that I had to
fix someone else's web application because it opened hundreds of
connections and stopped all users in their tracks when the number of
allowable connections was exceeded.

The problem is eliminated when I set the connection string to use a
SQL Server login instead of a trusted connection. Until I understand
what's going on here, I'm going to stick with that. Any insight into
what may be causing this would be welcomed.

I have no idea of what might be going on. I can understand that
connections are not being resued - the typical reason is that they
are not closed properly. But why it would work with SQL authentication
and not Windows authentication, I don't know.

My guess it that when you changed the authentication, you changed something
more thar you did not tell us about.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Bill E.]

Thanks for your comments Lyle, Mary and Erland.

Lyle -- I'm not sure how much Jet is involved in this because I have
no queries that need to be translated. Everything is a pass through
to SQL Server. There are no linked tables in my application. I've
seen that technique you mentioned for the ADO connection, but I'm not
having any problems with ADO. It's the pass-through queries that are
the problem.

Mary--Thanks for the resources. The first article deals primarily
with querying SQL Server through linked tables so does not apply to my
case. I'm interested in seeing what the ODBC trace looks like and I'm
hoping that it will not simply be an academic exercise but will lead
me to some kind of corrective action.

Erland -- I changed the connection string only, nothing else.

Bill