Re: Access 2007->SQL Server2005 "connection was forcibly closed",G

You may need to clean up the existing app if you intend to limp along
for a few more years until you can switch to a more appropriate
client. It's not an uncommon situation that applications started by
amateurs can grow to be unmanageable, but sometimes they can be
cleaned up by eliminating extra layers and simplifying data access by
substituting set-based operations (T-SQL, local queries, pass-through
queries) for client code. Take a hard look at the ADO code -- are you
leaking resources by not closing connections cleanly? As was pointed
out elsewhere, connections need to be returned to the pool to be
reused. Access was never intended to work out of the box with SQL
Server, although it often ends up in that space.

--Mary

On Wed, 19 Aug 2009 08:49:02 -0700, Christian Coppes
<ChristianCoppes@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi Mary,

thanks for the informations, I already read this document, it's very helpful.
The number of connections couldn't be the issue with this as the same
database with the same number of users worked without any problem on a
Enterprise version of SQL Server 2000. Today the admins told me that the new
one (SQL Server 2005) is a cluster of servers so they should have no problems
with network problems. Especially the problem occurs in LAN, not in WAN (as
fas as I know) so the bandwidth should not be the problem.
After reading a lot today I think the highest chance is that it has indeed
something to do with the network but I cannot proof that, I've no access to
router logs.

I did a lot of further tests today after I got more rights on the server.
1. I tried to use Performance Monitor, it shows that the number of
connections to this server (including all 36 databases) is around 200
connections so this should not be a problem. I tried to check the TCP
"Connection Reset" and "Connection Failure" (and so on) counters, they are
showing around 586,000 connections active and 402 connections established,
7553 connection failures and around 801,000 connection resets. I'm not sure
if this says something about the quality of the network packets.
Moreover I tried User errors/sec, which rarely shows one or two errors (not
knowing where they come from so the value is not very helpful) but it seems
that it has nothing to do with the time when the error in Access appears.
And I tried Batches/sec, which shows average 140 running batches, sometimes
jumping over 4000. So it doesn't look like the cluster is too heavily used.
2. I tried to use the SQL Server Profiler to check what happens if I open a
table and wait until the error occurs. The only thing the Profiler says is
"Audit Logout" in the moment the error occurs in Access. So it seems that the
connection is terminated elsewhere and SQL server releases the connection
because it sees that the connection is not used anymore (my guess). No error
message (or maybe I have not the right object in the trace to show me such an
error).
3. After I got access to the Activity monitor I looked for the connection
which had the error (filtered on my machine) and after the error occurs the
monitor shows "ASYNC_NETWORK_IO" as wait type and status "Suspended". If I
doubleclick on "command" to see what was last executed it shows me a select
to a completely different table I never touched in this client since starting
it...strange... (for testing purpose I changed this one client to another SQL
server user login to be sure that it is not mixed with other running clients).
If I open the table after waiting a while (a few minutes) the message in the
activity monitor is the same even if there is no error in Access.

I asked to get a SQL Server log file to make further investigations because
all this doesn't seem to give me any result for the error.

Yes, you're right, there are a lot better methods of accessing an SQL server
but unfortunately I have no choice because the application was not made by
me. It started as a simple Access database and was made by someone who is not
a database programmer, I solved a lot of issues with this and already
recommended to start over again completely with a new client, but it seems
that the application will not run long enough anymore (should be exchanged
with other software in a few years) so there's no time for rearrange anything.

Thanks for your posting,

Christian


"Mary Chipman [MSFT]" wrote:

In a nutshell, you can't use Access as a FE to SQL Server the same way
you would use it if the tables were local Jet/ACE tables running on
the client machine or a LAN. Once you start round tripping over the
network and holding tables/connections open, you cut down drastically
on the scalability of your application and limit the number of
concurrent users you can support. Check out this whitepaper

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

A couple of quick notes -- don't use ADO recordsets for modifying
data. Guaranteed slowest way of doing anything. Use pass-through
queries for fetching read-only data -- holds no locks on the server
(great for reports). Cache static data in local tables for combo and
listboxes. Basically you want to tread as lightly on the server as
possible -- imagine that you are creating a Web application, not an
Access application, and you'll be on the right track. If you go for
read-only data access and use stored procedures to post updates, an
Access client can be used to support an infinite number of concurrent
users. Not that you'd necessarily want to go to that much work (see
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/dp/0672319446 for the gory details), but it's
possible.

--Mary

On Tue, 18 Aug 2009 05:25:01 -0700, Christian Coppes
<ChristianCoppes@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... an Access database in former times this was a lot of work which took me ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... client for programming easy, fast and good applications. ...
    (microsoft.public.sqlserver.connect)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... The number of connections couldn't be the issue with this as the same ... Enterprise version of SQL Server 2000. ... server user login to be sure that it is not mixed with other running clients). ... It started as a simple Access database and was made by someone who is not ...
    (microsoft.public.sqlserver.connect)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... I think it will ameliorate the situation if you clean up the client as ... And to come back to my problem: I think with help of the SQL Server admins ... closed connections - but all of these errors are in the version which used ... Every new client opens again 30 connections if I open 30 tables ...
    (microsoft.public.sqlserver.connect)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... And to come back to my problem: I think with help of the SQL Server admins ... closed connections - but all of these errors are in the version which used ... communication between ODBC (OLEDB and Native Client, ... Every new client opens again 30 connections if I open 30 tables ...
    (microsoft.public.sqlserver.connect)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",GNE 1
    ... Optimizing Microsoft Office Access Applications Linked to SQL Server ... changing number of clients using the SQL Server with this frontend at the ... the following connection string: ... connection pooling or if these connections are separated connections). ...
    (microsoft.public.sqlserver.connect)