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

I think it will ameliorate the situation if you clean up the client as
much as you can, get the low-hanging fruit insofar as best practices
are concerned (the things already talked about in this thread). The
book is still in print because we wrote a lot of original code for
creating a lightweight front end that is still valid. Even though the
versions of Access and SQLS covered are old, the underlying
architecture hasn't changed as far as ODBC and data access is
concerned (ignore the chanpter on ADPs--that material is mostly
useless). Feel free to contact me if you have any specific questions
that are not of general interest to the newsgroups -- mary.chipman at
microsoft.com.

--Mary

On Fri, 21 Aug 2009 14:09:01 -0700, Christian Coppes
<ChristianCoppes@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi Mary,

ah OK...If I would have known that I wouldn't have wrote so much about
it...crashing through open doors...:-)))

In the meantime I got access to the Profiler, the Activity Monitor and
Performance Monitor of Windows so I could test a lot more. I saw what you
mean, it's very much overhead Access produces to open a simple select. I will
do my best to change this whereever I can.

Amazon didn't tell much about the contents of your book. If it is not about
"How do I move my mouse to open a form?" (which are often theme in books
saying it is for professionals) then maybe I can learn from it and find some
good tips? In most cases things that were good for older versions of
Access/SQL Server are good for new versions in the same way, I often buy
older books if they are good.

And to come back to my problem: I think with help of the SQL Server admins
it should be possible to find the reason for the ODBC problem. It would be of
help if someone has experience in the settings of SQL Server (the admin
maintainable settings) or TCP settings maybe from Windows. I have not so deep
knowledge about the transport protocols and all their settings. Unfortunately
the admins seem not to be specialists in SQL Server nor very deep in Windows
so I must find the problem lastly on my own.

Cheers,

Christian


"Mary Chipman [MSFT]" wrote:

Hey Christian, I think you misinterpreted what I said about Access not
being used out of the box with SQLS -- I actually wrote the book on
how to do it successfully -- http://www.amazon.com/dp/0672319446 :-)
What I meant was that opening entire linked tables has consequences
that opening a table in Jet doesn't have -- network bandwidth, server
load, potential locking/blocking/deadlock issues, so the
out-of-the-box behavior of basing forms and reports by specifying a
table name or a "select * from" query causes problems with SQLS that
do not exist when Jet/ACE is used as the data store. Access caches
data on the client computer when used with Jet, which is not possible
with server data--locks are held on the server in most cases. You can
cache static data in local Jet tables for using with combo/listboxes
and subforms, and you will get the best performance and have fewest
problems if you fetch data for browsing read-only by means of a
pass-through query, and fetch only single records for editing (use
query-by-form techniques to filter data).

It's a drag that you can't get a Profiler trace going so that you can
see for yourself what's going on at the server. I know what a drag it
can be to try to fix something when you don't have all the information
you need.

--Mary

On Fri, 21 Aug 2009 06:21:01 -0700, Christian Coppes
<ChristianCoppes@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi Mary,

that's what my job is with this application: Make it better, change it to a
database application. Before I began to work with it it didn't use any ADO
code, everything was made with very Access like methods like "DLookup", DAO,
saved queries and so on. All tables were linked with a System DSN, I changed
it to use DSN less method. I moved every table I was able to move to the SQL
Server to make it multi user accessable and a lot more such things. As it was
an Access database in former times this was a lot of work which took me
months.
I think there will be a lot of errors and problems in the code including not
closed connections - but all of these errors are in the version which used
the SQL Server 2000 (see my reply to Erland about this) and everything worked
here.
As I reported: The problem occurs and is reproducable with only Access is
open and only some tables will be opened by using doubleclick on the table in
the Navigation pane of Access - that means, no part of the application is
responsible for the General Network Error, it is anywhere in the
communication between ODBC (OLEDB and Native Client, both the same) and the
SQL Server 2005. After all my tests I wrote in the answer to Erland I'm sure
know that this is a problem with some configuration.

Your last statement simply is not true: Access was made for direct access to
most databases but especially to SQL Server since at least Access 97. Since
then Access supports the creation of Access projects which directly uses the
SQL Server without any use of the Jet engine. And Access is a very powerful
client for programming easy, fast and good applications. Dot Net is the first
library which is capable of being better but it's not so easy and fast to
create an application with this. There's a lot more work needed and more work
needs more time so in many cases Access as a well-known application is the
better solution to get a middle-sized database up and working than any other
client. This means of course good planning and good knowledge of database
technology but this is the basis in all other cases, too. You can create
shitty programs in every language, no problem...:-)

Cheers,

Christian


"Mary Chipman [MSFT]" wrote:

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.

.