Auto-Recover when DBNETLIB ConnectionWrite General network error causes ADO connections to go offline in Delphi applications?
Well nobody has answered this question, and I think that some follow-up would be helpful.
Here is what I have learned:
There are no reliable situations where in a test environment you can reproduce this General Network Error. That is to say, we're dealing with Irreproducible Results, which is where many developers leap into evil hackery in an attempt to "monkeypatch" their broken systems.
Fixing the underlying fault has always and everywhere been better than fixing it in code, when the SQL library gives a "General Network Error". No repair has ever been shown to be possible, because usually it means "the network is so unreliable that TCP itself has given up on delivering my data", this happens when:
You have a bad network cable.
You have duplicate IP addresses on a network.
You have dueling DHCP servers each handling out different default gateways.
You have local ethernet segments that have poor connectivity between them.
You have an ethernet switch or hub which is failing.
You are being intermittently blocked by a malfunctioning firewall.
Your customer may have changed something on their network, and may now be unable to use your software. (This last one actually happens more than you might think)
Someone may have configured an SQL Alias using
cliconfgor other client side configuration elements that are specific to a single workstation's registry settings, and this local configuration may result in bad behaviour that is difficult to diagnose and may be limited to one or several workstations on a large network.
None of the above can be detected and reported either at the TCP or SQL level. When SQL finally gives up, and it gives this "General Network Error", no amount of cajoling from my software is going to get it to un-give-up, and even if it did, I would be doing a "try/except/ignore" antipattern. This error is so severe that we should raise it all the way up to the user, log it to disk in an error log, give up (quit the program), and tell the user that the network connection is down.
I have seen this happening due to bad coding too..
If you open a recordset using a connection and if you reuse that same connection in a loop for another recordset while the first connection is not closed then that can cause similar errors.
Another occasion very rarely on web applications is while the application pool is recycling you may receive similar error.
We have different sites in a same server where I have noticed that with the same application but with different customisations, only one site is causing this issue. That leads to the above findings.
This blog helped me to find the issues:
http://offbeatmammal.hubpages.com/hub/Optimising_SQL_Server