Trapping a SQL Server Disconnect

[David W. Fenton]

I have a client with an app that has a SQL Server back end. They are
experiencing network problems that I am trying diagnose and fix, and
occasionally the connection drops and Access loses its head and has
to be closed.

Is there an error I can trap for in the Form Error handler of my
forms that would allow me to do a retry after a certain delay?
What's happening is that the connections are dropping for really
short periods of time and coming back up (as evidenced in Outlook
and with mapped drive letters; and no, I'm not responsible for the
design of this network, or there'd be no mapped drive letters).

It's not a permanent solution, but it's better than the users having
to force quit Access from Task Manager.

Fortunately, there's no danger to the data, but I just want to
relieve them of the annoyance until the network problem is resolved
(I know perfectly well that these things can be extremely difficult
to troubleshoot).

 

[Arvin Meyer [MVP]]

David,

Dropping any connection to bound data can cause corruption, even in
SQL-Server. It's far more difficult to fix if it happens, too.

As far as I know, 1 idea that you could do is to put a file in the
SQL-Server database folder, that you can periodically poll. There also may
be some code on the net that would periodically check the Event Log on the
server for a specific error. I found some VB code that looks like it may
work in Access that pings another computer:

http://kandkconsulting.tripod.com/VB/VBCode.htm#WinNet

Constant pinging or polling can't possibly be good for performance, so I'd
suggest getting a hardware guy in ASAP to start checking the network.

 

[david]

I've never heard of anyone being able to successfully recover
from a connection timeout, so I'm not hopeful about recovering
from a dropped connection.

The form error handler handles data errors on bound forms.
Do you have a form bound to an ODBC data source?
Try this:
Put a timer on the form, and check the recordsetclone
StillExecuting property. If it is true, cancel the task.

Will it work? See paragraph 1.

(david)

 

[David W. Fenton]

Dropping any connection to bound data can cause corruption, even
in SQL-Server. It's far more difficult to fix if it happens, too.

What kind of corruption could it cause? I am absolutely certain that
if we hadn't upsized to SQL Server a year ago, they'd have had many
incidents of a corrupted back-end MDB. The SQL Server hasn't given a
single hiccup itself -- it's only the network connections that are
causing problems.

As far as I know, 1 idea that you could do is to put a file in the
SQL-Server database folder, that you can periodically poll. There
also may be some code on the net that would periodically check the
Event Log on the server for a specific error.

There are no errors in any of the server logs.

I found some VB code that looks like it may
work in Access that pings another computer:

http://kandkconsulting.tripod.com/VB/VBCode.htm#WinNet

Constant pinging or polling can't possibly be good for
performance, so I'd suggest getting a hardware guy in ASAP to
start checking the network.

I'm the hardware guy, and along with the other guy involved (also a
hardware guy), we've been working on this for quite some time. We
can't find a damned thing wrong with the network or any of the
hardware involved (we've already replaced the switch twice, change
the NICs in all the computers and redone all the patch cables; we've
also had the wiring guys back in to test the wiring, which was
brand-new in February). That's why I'm looking for some kind of
software amelioration to patch over the problem until we solve it
for good. Based on what's happened so far, it's been nothing but an
annoyance. If I can trap for some SQL Server errors and retry after
a certain interval, then that would mean they wouldn't have to force
quit Access (which can't be good no matter your back end).

 

[David W. Fenton]

I've never heard of anyone being able to successfully recover
from a connection timeout, so I'm not hopeful about recovering
from a dropped connection.

Are you sure? If I knew the error numbers involved, I'd start
testing it, but you know how users are -- they never seem to be able
to supply an error number when they report a problem.

The form error handler handles data errors on bound forms.
Do you have a form bound to an ODBC data source?

Yes, via linked tables.

Try this:
Put a timer on the form, and check the recordsetclone
StillExecuting property. If it is true, cancel the task.

Will it work? See paragraph 1.

I would never use a timer, which is worse than the original problem.

 

[Armen Stein]

What kind of corruption could it cause? I am absolutely certain that
if we hadn't upsized to SQL Server a year ago, they'd have had many
incidents of a corrupted back-end MDB. The SQL Server hasn't given a
single hiccup itself -- it's only the network connections that are
causing problems.

We have many Access applications using SQL Server databases. In fact,
I've used one of our internal applications over a wireless network in
my home, then across the internet using Comcast cable (now Verizon
FiOS). That connection has dropped many times over the years. We've
NEVER seen the SQL database corrupt. Even if the connection drops at
the precise moment of an update (rare), SQL Server seems to handle it.

But I agree with others that we've found no good way to test and
re-establish the SQL connection from Access once it has been lost. We
find that we must close and reopen the Access app. I (and others)
have asked the Access team to improve this.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Arvin Meyer [MVP]]

I have seen 1 SQL-Server corruption due to, what I believe was a dropped
connection, and 2 other that were not. The one, was an adp with bound forms.
Since the front-end locked up in the middle of a write, I came to that
conclusion.

 

[Arvin Meyer [MVP]]

Judging by the steps you've taken to find the problem, I can only think of a
few other possibilities:

1. The most likely ... user error
2. Bad video card or driver
3. Virus (like Slammer)
4. SS logs need to be cleared

 

[David W. Fenton]

I have seen 1 SQL-Server corruption due to, what I believe was a
dropped connection, and 2 other that were not. The one, was an adp
with bound forms. Since the front-end locked up in the middle of a
write, I came to that conclusion.

I'm not using an ADP, so you don't have any examples at all of it
with MDB/ODBC, right?

I just didn't think it was possible for a client error to cause SQL
Server to corrupt data. It just makes no sense to me that it would
even be theoretically possible.

 

[David W. Fenton]

Judging by the steps you've taken to find the problem, I can only
think of a few other possibilities:

1. The most likely ... user error

It isn't. I am certain of that.

2. Bad video card or driver

It's happening on multiple machines, with different hardware and
different video. And the same hardware did not have any problems for
many months, until the move to a new office. Yes, of course, we are
mostly looking at things relating to the move to the new office, but
that hasn't turned up anything at all.

3. Virus (like Slammer)

The server is completely clean -- we just spent hours on Friday
confirming that.

4. SS logs need to be cleared

The logs are being archived and cleared approximately once a month,
and there's nothing in the current log to indicate any problems at
all. The problem has been occuring over a period of time where the
newly-cleared logs would have been anywhere from empty to the
current size of a little over 100K. I can't see how this could be
problematic, or a cause of the problem, since there's no correlation
between log size and when the problem has been occuring.

 

[Sylvain Lafontaine]

Logical corruption or a database corruption? If you have many
tables/records to update inside a single logical transaction, a dropped
connection in the middle of these writes will give you a logical corruption
because ADP doesn't give you a way to control the state of a transaction
when you are using a bound form. (You can make these multiple writes into a
single transaction but only at the cost of using unbound forms in order to
get the full control over the transactions mecanims.). The exact same kind
of problem can occurs with ODBC linked tables in a MDB or ACCDB database
file.

However, I don't see how you could make the SQL-Server to be the culprit
here. The poor transaction control for bound forms is clearly a big
limitation of Access and SQL-Server has nothing to do with it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

 

[Arvin Meyer [MVP]]

I just didn't think it was possible for a client error to cause SQL
Server to corrupt data. It just makes no sense to me that it would
even be theoretically possible.

I know of no examples with an mdb.

Of the 3 serious SQL-Server corruptions I've dealt with, 1 was due to the
logs not being properly maintained, and it took the entire server down, 1
was due to the Slammer virus, and was fixed, and 1, the adp, occurred during
a large write to the server.

Any file can be corrupted if interrupted during a write. The advantage of
SQL-Server over JET though is that it is easier to restore corrupted data
because of the log. If using unbound forms, the write is a brief burst,
which also greatly reduces the chance of corruption over bound forms.

 

[Arvin Meyer [MVP]]

How is the power in the new office? Is it clean? or very spiky? How about
the UPS? Is that clean?

Lastly, and then I admit defeat, can you timestamp every process to a text
file, and possibly catch the problem that way?

 

[Sylvain Lafontaine]

But unlike with JET, parts of the file with SQL-Server never travel over the
wire. The client at the other side of the wire never manipulate the file
directly; it only sends instructions to the SQL-Server to make some writing.
Any incomplete instruction - created by a disconnection - is simply
discarded by the SQL-Server and if this instruction was part of a global
transaction, the whole transaction is automatically rolled back by
SQL-Server.

There are still some possibilities of database corruption, for example
because of a hard-drive failure or a memory error on the server but the
chance of a database corruption created from a disconnected client are
probably close to zero. It's not really impossible because there are still
the possibilities of a hard-drive, controler or memory failure to occur when
the SQL-Server try to roll back the transaction but again, these are created
by a hardware failure on the server and not by a communication failure
between the client and the server.

On any good modern server, the probability of a hardware failure is many
orders of magnitude lesser than the probability of a communication failure;
the later beeing quite common.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

 

[David W. Fenton]

I know of no examples with an mdb.

Of the 3 serious SQL-Server corruptions I've dealt with, 1 was due
to the logs not being properly maintained, and it took the entire
server down, 1 was due to the Slammer virus, and was fixed, and 1,
the adp, occurred during a large write to the server.

I can't see how this last one could have been caused by the client
connection. Surely something happened on the server at the time the
ADP client was writing.

Any file can be corrupted if interrupted during a write. The
advantage of SQL-Server over JET though is that it is easier to
restore corrupted data because of the log.

That's not the only advantage. The writes are local to the server,
i.e., usually your SQL Server data store is stored on the same
machine as the SQL Server is running, so there's no network in
between the server process and the actual data store itself.

If using unbound forms, the write is a brief burst,
which also greatly reduces the chance of corruption over bound
forms.

I don't use unbound forms very often since there's little point in
using Access if you're going to avoid bound forms.

 

[David W. Fenton]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

But unlike with JET, parts of the file with SQL-Server never
travel over the wire. The client at the other side of the wire
never manipulate the file directly; it only sends instructions to
the SQL-Server to make some writing. Any incomplete instruction -
created by a disconnection - is simply discarded by the SQL-Server
and if this instruction was part of a global transaction, the
whole transaction is automatically rolled back by SQL-Server.

There are still some possibilities of database corruption, for
example because of a hard-drive failure or a memory error on the
server but the chance of a database corruption created from a
disconnected client are probably close to zero. It's not really
impossible because there are still the possibilities of a
hard-drive, controler or memory failure to occur when the
SQL-Server try to roll back the transaction but again, these are
created by a hardware failure on the server and not by a
communication failure between the client and the server.

This is precisely why I can't imagine that a client disconnection
could corrupt a SQL Server data store.

 

[David W. Fenton]

How is the power in the new office? Is it clean? or very spiky?
How about the UPS? Is that clean?

There's a UPS only on the server (and its switch), but not on the
workstations. There's nothing in the event logs, and I'd expect
there to be warnings from the UPS software if there were power
problems.

Lastly, and then I admit defeat, can you timestamp every process
to a text file, and possibly catch the problem that way?

Not sure what you mean here. Can you explain?

 

[Sylvain Lafontaine]

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in

This is precisely why I can't imagine that a client disconnection
could corrupt a SQL Server data store.

The chances are low but not zero. However, I think that somewhat the
opposite that happens: an hardware failure occurs on the server but people
(automatically) put the responsability of the database corruption that
follow - if there is one - to a connection failure.

When you see the great number of JET database corruptions that happen
because of a connection failure - sometimes, it's like they're raining -
it's easy to start thinking that every database corruption that occurs on
any database system do so because there have been a communication failure.

 

[Arvin Meyer [MVP]]

The chances are low but not zero. However, I think that somewhat the
opposite that happens: an hardware failure occurs on the server but people
(automatically) put the responsability of the database corruption that
follow - if there is one - to a connection failure.

In the case that I mentioned, I doubt it was hardware failure. One page of
records and the system tables were corrupted, but I was able to get at the
rest of the data to save it. Once the database was rebuilt, there were no
further problems running on the same machines.

 

[Arvin Meyer [MVP]]

I'll tell you what. Google for "SQL-Server database corruption" and start
reading. It's been a long time since I've been to SQL-Server seminars, but
every dba I've ever met at one had a corruption story. Don't get me wrong,
I'm not saying that SQL-Server is unstable. That's not true. It is very
stable. What I am saying is, that although rare, it is possible.