none
Communication Link Failure Error

    Question

  • Hi,

    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:

    driver={SQL Server};server=AA;uid=BB;pwd=CC;database=DD

    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.

    Please reply.

     

    Thursday, October 12, 2006 1:32 PM

Answers

  • It 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.
    Thursday, October 12, 2006 6:26 PM

All replies

  • It 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.
    Thursday, October 12, 2006 6:26 PM
  • 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.

    Please reply.

    Friday, October 13, 2006 9:06 AM
  • Good day,

    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.

    regards

    Monday, November 20, 2006 1:13 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

    DDD= password

     

    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.

     

    Regards,

     

    Tahir Sultan

     

    Monday, November 20, 2006 3:11 PM
  • Tahir thanks for the response.

    We still have a problem because we can only use ODBC?

    regards

    Amanda

    Tuesday, November 21, 2006 11:56 AM
  • 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.

    HTH

    Tuesday, November 21, 2006 8:30 PM
  • 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.

    regards

    Wednesday, November 22, 2006 7:37 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.

    Tahir Sultan

     

    Wednesday, November 22, 2006 9:43 AM
  • We 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?
    Thursday, January 18, 2007 2:44 PM
  • 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!!!

     

    Regards

     

    VHusain

    Tuesday, November 25, 2008 6:12 AM