August 10, 2010 at 7:54 am
Hi all,
We are facing this error intermittently when connecting from the application server to SQL server using ODBC
Error:|(0) - [Microsoft][ODBC SQL Server Driver]Communication link failure
We have sql server 2005 databases, we use SQL server ODBC on the application server version 2000.86.3959.00 FIle SQLSRV32.DLL dated 2/18/2007.
The error looks like something to do with the connection issue.. Do I need to update the ODBC driver to any latest versions? I'm not able to find any good information on this when I researched..
Thanks for all your help
August 11, 2010 at 3:36 pm
I would suggest that you get rid of ODBC and use OLEDB instead. Much more secure, much faster and by far more reliable.
ODBC died in the 1980's
Andrew SQLDBA
August 12, 2010 at 3:15 am
We use ODBC and SQL Server for our entire application. There is definitely a problem with intermittent loss of connection resulting in "Communication Link Failure" - we see this occasionally, usually on a connection which has been idle for some hours. Unfortunately the only workaround we have come up with is to drop the connection and reconnect when we detect this error.
My guess is that this is a problem in the SQL Server network layer - which would not be solved by changing to a different client-side technology such as OLEDB. I hasten to add that I have no evidence for this - if there really is a fix for this I would love to know.
August 12, 2010 at 8:53 am
Exactly, you should never allow a connection to consume resources when it is not being used. That is the big drawback to the old fashioned ODBC. Eventually SQL will run out of memory and time on the processor to support all those open connections when there is no data transfer.
OLEDB opens the connection at the last possible second, performs the query, and closes the connection immediately when the recordset is returned.
You are experiencing this drop in the connections because the connection manager will time out, but not drop the connection. This is a very old fashioned way of doing this.
Either change the connections or live with it the way it is. There is no in between. What will cost you more? Making a simple change of the connection, or hiring some poor guy to sit there and monitor when a connection has gone stale and then manually drop it? It would take about two full days to modify the connections, test them and allow the users to hit the database again.
Andrew SQLDBA
August 13, 2010 at 12:34 pm
My biggest drawbacks are the use of connection pooling whether it is done in .Net or Java it tends to consume more resources and never freeing them up!
August 16, 2010 at 3:06 am
We had same problem couple of months ago with a PC running in the domain. To solve this problem, we manually created an DSN under administrator account, with TCP/IP connection explicitly specified, and then we started the program and problem was eliminated.
Commit to Never Ending Improvement
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply