Access 2007->SQL Server2005 "connection was forcibly closed",GNE 1

Hi,

with an Access 2007 application, I have a very big problem connecting an SQL
Server 2005.

The scenario:
- nearly 200 clients with Windows XP Professional (in an Active Directory
Domain)
- clients uses WAN, LAN and WLAN, different locations
- one SQL Server 2005 EE, uses actually 36 databases, the Access 2007
application uses 2 of them (one for testing, one for production)
- around 50 tables in the database the application uses
- changing number of clients using the SQL Server with this frontend at the
same time
- the clients are using Access 2007 runtime and a ACCDR (from ACCDE)
version of the frontend, I'm using a full version of Access 2007

The problem:
From time to time Access shows an error message while some of the tables
were left open:
[Microsoft][SQL Native Client]TCP Provider: An existing connection was
forcibly closed by the remote host.
(#10054)[Microsoft][SQL Native Client]Communication link failure (#10054)

It seems that the problem occurs more often if more clients are using the
same table at the same time.

I searched the Internet up and down for the problem and found some
solutions. One of them was, that there could be an issue with tables which
using Memo fields (any SQL Server type which is longer than 255 characters is
converted to "Memo" in Access). The most problematic table used 5 "ntext"
fields. Following this knowledge base article
http://support.microsoft.com/?kbid=942861
I programmed a solution to cut all fields in single 255 character strings
and save them separately into an extra table. This is working and so the
mentioned table doesn't have any ntext field anymore, only an int field which
targets to the MemoField table (consisting of an ID and a nvarchar(255)
column).
Unfortunately the solution didn't solve the problem.

The application uses two different methods to access the tables on the
server. First the tables are linked with a DSN less connection using the
following connection string:

ODBC;DRIVER=SQL Native
Client;SERVER=FullQualifiedServername\Instance;UID=Username;PWD=Password;APP=2007 Microsoft Office system;DATABASE=DatabaseName;

The second method is inside the VBA application where ADO is being used to
access queries and tasks like INSERT, UPDATE and so on. This is be done with
the following connection string:

strConnectionStr = "Provider=SQLNCLI;" & _
"Server=" & cActualDBServer & ";" & _
"Database=" & cActualDatabase & ";" & _
"UID=" & cActualDBUser & ";" & _
"PWD=" & cActualDBPassword & ";"

where the constants for the right parameters are set elsewhere in the
application with the same contents as in the other connection string.

As I'm only database owner on the SQL Server I unfortunately cannot run any
tests there, I have no access to the SQL Profiler, Logs, Activity Monitor or
Performance Monitor so I cannot test anything there.
As a test alternative I installed an SQL Server Express 2005 in a Windows XP
virtual machine on my computer which has the same configuration as the
standard clients. I copied anything of the normal SQL Server to this smaller
version and relinked a test version of the application to this. Anything else
is the same here.

I tried to run a Performance Monitor Log here and started 5 clients to use
this database. Then I opened any table without starting the application ( the
VBA part) only using the Navigation pane of Access, with all of the 5 clients.

What I saw on the Performance Monitor is that each opened table creates a
new connection on the SQL Server (I'm not sure if this is part of the
connection pooling or if these connections are separated connections). So if
I open 30 tables with the Navigation pane, I got 30 connections on the SQL
server. Every new client opens again 30 connections if I open 30 tables
there. Every client uses the same user and password using SQL authentication
and the same connection string so I would expect that they uses connection
pooling but as mentioned, I'm not sure and I don't know how to test that.
I saw other effects:
- opening a table in the Navigation pane works very fast, opening a view
which uses the same table on the SQL server has a big delay in execution (but
this is maybe an effect of having all on the same computer). The VMWare
player is accessed through network with an own IP, as the big SQL Server.
When I open a view as recordsource with a form it is normally faster than
using a linked table as recordsource.
- if I let the tables opened in all clients and simply wait, the
connections in the Performance Monitor are closed after around a minute
(which is what I expected as I read that this is the normal behaviour of SQL
Server to close not used connections after a minute). They are not reopened
if I go to one of the opened tables and refresh them.
- if I reach around 96 connections, every further try to open another table
runs with more delay. Views are very slow.
- if I come to more than 100 connections (which is no border, it opened 115
connections without any error) views cannot be opened anymore, after waiting
Access shows an ODBC timeout error. Looking in the SQL Server Express logfile
it shows the error, too:
Error: 8645, Severity: 17, State: 1. A time out occurred while waiting for
memory resources to execute the query. Rerun the query.

I guess this is simply too much for one computer running VMWare, SQL Server
Express and 5 clients opening 30 tables each...:)
But: It is not the error I got with the "big" SQL Server.

My guess was, after the thing with the Memo fields was not solving the
problem, that the problem has something to do with the connection - that all
users connects with the same credentials. The above test shows that it
normally couldn't have anything to do with the number of connections to the
server. If the standard "Max Pool size" is 100 it would mean that the
application would have crashed trying to use more than that. 115 connections
shows, that it worked with the simple Express Edition. The EE Server has 36
databases with an unknown number of users working with it. As both server
versions can use a maximum of 32767 connections (I checked the setting on
both servers) I don't believe that our application will get the EE server
above this number of connections. In my test I opened 30 of 50 tables at the
same time - but if the application is started normally no user uses more than
5 of them at the same time. With 200 users I don't believe that the number of
connections will go over 1000 connections at the same time.

The server will get a priority handling on all the switches in near future
to make it faster. But I don't think this will solve the problem.

Other facts:
- the problem most often occurs with the above mentioned table and nearly
no other table. It is used by around 20 users at the same time
- the complete database was on a SQL Server 2000 EE before with the same
settings without any problems
- the connection string used the older OLE driver before, for testing
purposes I now use the SQL Native client on all clients (installed this
driver on all workstations) - the problem is the same.
- the problem occurs if I only open the table and let it be opened with the
Navigation pane of Access while other users are working with the application.
If I'm alone (or only a few clients are online) the problem doesn't occur
that much or disappears completely until more users are online again (the
problem occurs in a form which uses this table in a running application, too).
- as there are a lot of other users/databases on the same server I don't
think that the server has capacity problems with network bandwidth or other
such things.
- as the problem only occurs with the Access ODBC connection (recordsource
of a form uses the linked tables in any case, a view is a linked table in
Access and linked tables can only be linked with ODBC driver) I guess that
the problem has something to do with ODBC (the error I wrote above is an ODBC
error). If I use SQL Management Studio (2005/2008 Express) I never got any
error accessing any table or view. As far as I see no error occured accessing
the database through ADO commands with the other connection string.

Thanks for reading this very long article until here...:)
If anyone has any idea what could be the reason for this annoying error it
would be great if we could discuss it.

Cheers,

Christian
.

Relevant Pages

  • Re: MsgCommunicator v.2.00: Instant Messenger SDK, now with databases support
    ... expect persistent connections. ... they will wait for the server to pick them up. ... your Clients can stay "off-line" for about 30 minutes before they have to ... requests *simultaneously*. ...
    (borland.public.delphi.thirdpartytools.general)
  • sockets, closing and TIME_WAIT
    ... During heavy load the server can't follow anymore because the sockets ... my server should be able to handle 10 clients connecting ... This gets a free position in the array of connections, ...
    (comp.unix.programmer)
  • Re: TCP/IP redundant connections
    ... The clients have persistent TCP connections to the server, ...
    (freebsd-hackers)
  • Re: Intermittent Network Connections
    ... I've just reconnected the Server LAN nic 1 to the ethernet switch (the same ... Server IP config and Client IP config attached. ... > turn is connected to an ADSL modem out to the internet The clients connect to ... >> Clients can sucessfully log in but periodically loose their connections. ...
    (microsoft.public.windows.server.sbs)
  • Re: SBS 2003 R" Premium stops responding to clients
    ... Sometimes the SBS lost DC ... when all clients were down and there are no sql ... The sql load on this system is very small, and there were no sql server ... Mem is 2 GB and there are 10 clients. ...
    (microsoft.public.windows.server.sbs)