Thursday, October 12, 2006 1:32 PM
I am using ADO in my application to connect and retrieve data from Microsoft SQL Server 2000 SP4. When I start my application, it establishes connection to the server successfully and I am able to retrieve data from the server. here is my test scenario:
1. unplug the network cable from the client machine and put the cable back in after some time. I disconnect from the database in my code when the client machine disconnects from the network
2. Once the client machine comes back on the network, I try to reconnect and retrieve data from the sql server but I get the following error message when I try to execute a sql command:
[Microsoft][ODBC SQL Server Driver]Communication Link failure
This problem occurs only with Window XP SP2. With XP SP1 it works fine. It seems to be problem on the client side as with Windows XP SP1 I do not get this error message at all.
Here are the various details:
1. The firewall is off on the client workstation.
2. the connection string is:
where AA = DNS name of server machine where sql server is running.
BB = user name
CC = password
DD= database name
3. The client is remote to the server.
4. I can ping the server machine when the client machine reconnects to the network.
5. I can still telnet the server when the client machine reconnects to the network.
6. client database provider is MDAC odbc.
7. the client and server machines are in the same domain.
8. Shared memory and TCPIP protocols are enabled and TCP is at the top of the list.
9. No alias is being used.
10. Viris scan is not installed.
11. force encriptioned is NOT checked.
Thursday, October 12, 2006 6:26 PMIt appears a connection pooling issue. The closed connections by the client app are returned to the pool and become invalid after you unplug the cable. Next time you try to connect, odbc reuse the invalid connectin and throw the error. Retry your connection or disable pooling should resolve your issue.
Friday, October 13, 2006 9:06 AM
I am already doing this:
1. When the machine disconnects from the network, I close the database connection. when the machine reconnects to the network, I try to establish a new connection to the database.
2. The connection pooling is also disabled for SQL server odbc driver under Connection Pooling tab of ODBC Data Source Administrator.
Monday, November 20, 2006 1:13 PM
Tahir posted this request in Oct this year, has there been any response yet?
We are experiencing exactely the same problem in completely different ODBC client and are desperately looking for a way around this.
Monday, November 20, 2006 3:11 PM
I eventually managed to find a way around it. Here are the changes which I made to my code:
1. Changed the connection steing to use native OLE DB as follows:
Provider=sqloledb;Data Source=AAA;Initial Catalog=BBB;User Id=CCC;Password=DDD
where AAA = SQL Server name
BBB = database name
CCC = login user name
2. I declare a list which holds pending SQL request.
3. hook up ADO connection object's Connectioncomplete and Disconnection events.
4. when execution of a SQL query failes, an exception is raised---catch this exception. check the list of pending requests. if it is empty then add this failed request into the list and disconnect from the database. (this will force to disconnect from an invalid connection if there is any). Do not attempt to disconnect from the database if there is already a pending request in the list.
5. I have a separate thread which attempts to connect to the database. once I have got connection to the database, i suspend that thread.
6. in disconnection event handler, I check if there is an error while I tried to disconnect from the database. If there is NO error then I resume the thread which attempts to connect to the database.
7. in connection event handler, I check if there is an error while I tried to connect to the database. If there is NO error then I start a timer which checks pending request list and if there is any pending request, send it to the SQL server and then empty the list.
Tuesday, November 21, 2006 11:56 AM
Tahir thanks for the response.
We still have a problem because we can only use ODBC?
Tuesday, November 21, 2006 8:30 PM
Can catch the error and retry the connection be one of your option?
Otherwise, please post your code here and we can try to repro it.
Wednesday, November 22, 2006 7:37 AM
At the moment we are diconneting and then re-connecting, but even this is not solving the problem. We need to stop the application to get rid of the error. This is only the case for the communication link failure error with all other errors diconnecting and re-connecting works fine.
Wednesday, November 22, 2006 9:43 AM
I had the same problem when I was using ODBC. Even disconnecting and reconnecting did not work at all. I tried all possible ODBC driver configuration but none of them seemed to make any difference. But moving to native OLE DB driver solved the problem.
Thursday, January 18, 2007 2:44 PMWe have a situation common to laptops. I'm convinced that it is the result of some power management setting (hibernate, hard drive shutdown, or standby), but can never log it to pinpoint it exactly. Any suggestions?
Tuesday, November 25, 2008 6:12 AM
After Googlizing and searching the whole internet, I could not come to any exact conclusion.
I happened to see the application log in the Event Viewer...and lo and behold!
I saw Automatic Updates were sometimes starting and stopping the SQL Server(Instance) Services. When Automatic Updates were disabled the errors reduced drastically.
Another reason was that the network card was constantly connecting and disconnecting. We replace the network cards and the error is as good as gone.
So I have written this so as to provide my solution to anyone who had this menace of a problem
[Microsoft][ODBC SQL Server Driver] Communication Link Failure
This is how I solved this problem.
Best of Luck!!!
Monday, November 30, 2009 1:10 PM