Communication Link Failure Error

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.

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.|||

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.

|||

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

|||

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 raisedcatch 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

|||

Tahir thanks for the response.

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

regards

Amanda

|||

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

|||

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

|||

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

|||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?