One of our clients has an application (FoxPro 9) running on top of a SQL Server 2005 backend. Intermittently, they are losing their ODBC connection with the SQL Server database. Below is the intial error information:
Err Msg: Connectivity error: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
ODBC Err Msg: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
SQL State: 01000
ODBC Err No: 10054
ODBC Handle: 1
FoxPro Error No: 1526
We cannot duplicate this error on command. We have tried any number of solutions to no avail. One such hardware base solution which we found was described in: http://support.microsoft.com/kb/942861/en-us
I mention this because it almost perfectly matches what we have been seeing. However, we have implemented all the workarounds listed in that posting (and in this one http://support.microsoft.com/kb/948496 ) - and the problem still continues.
This issue seems to show itself after the execution of long running queries, but we are not receiving any timeout errors, either from the application, or from SQL Server. I do not believe that this is the result of an idle timeout, because it sometimes occurs in the middle of an executing program.
I am not a hardware guy, but both the network, and the server (Windows Server 2003), appear to be fast and well designed. There are times however, when the database server is under significant stress.
If anyone has any suggestions on things we could try...please let us know!