Losing Connectivity to SQL Server (even locally)

[Adrian Whittell]

CTD3 PTF3
Windows XP SP 2
SQL Server 2005 SP Latest

We have had a problem for years where many of our customers on ALL our apps have stability problems when connecting via ODBC to SQL Server (2000 and 2005)
If a site is using our product and there is a minor glitch in the network and the connection is lost for a second when the user tries to run the next sql statement you get the following error:-

[Microsoft][ODBC SQL Server Driver]Communication link failure

At first we put this down to unstable networks and didn’t really act on it.
However, the problem happens on a local machine with a local SQL Server as well.
If you disconnect the network cable for 5 seconds (sometimes longer maybe 15 seconds) and put it back, when you call the next line of SQL (No results sets open by the way and user not actively doing anything), you get the error.
It also happens when you aren’t actively using the application.
So if you go and get a cup of tea and there is a network glitch for a second or so, the user will get the error the next time ANY SQL is run. (We have a lot of remote users)
It is the SqlPrepareAndExecute satement that is failing.
But this is after a successful SqlConnect statement.
There seems to be no tolerance in CTD to network glitches.
I'm pretty sure this is an ODBC problem but just wonder if there are any solutions?
We have some old VB apps and they have the same problem using ADO via ODBC. Connection via OLEDB can fix VB connections.
I think you can only use ODBC to connect to SQL Server via CTD so it may be something we cannot fix??

Our users are getting very frustrated with this and want us to provide a solution.

Any help would be much appreciated

ps Upgrading to later versions of CTD is not an option as we have tried 5.1 and 5.1 is clearly not ready for the size of of our apps just yet. (Needs a few more PTFs)

Adrian
Tribal

[jmcnamara]

We're on a WAN with 150 users all over the country and network glitches are indeed rare. Rather than trying to work around the problem wouldn't it be better to fix the network?

[Adrian Whittell]

It would be great for us to give this response to our customers. Unfortunately we are dealing with over 80 local education authorities (many with multi-sites). Each have a separate IT section, usually a third party company which work remotely from our actual customers. So this is not really an option. We just can't control all those networks.

I just can't see why there is no tolerance (especially when the SQL Server is on the same machine as the Centura client and both client and server are still up and running after simply disconnecting the network cable for 10 seconds).

I have looked at various forums and it's one of those issues where loads of people seem to have had the problem but at the end of every thread a solution is never reached after various suggestions - Add to hosts file etc. Very frustrating!

Adrian Tribal

[jmcnamara]

The IP address for the ODBC connection is most likely 'localhost'. Open the status window for your network connection, unplug the network cable and watch what happens to the IP address for the localhost - unless the site is not using DHCP and the work station address is fixed (which I don't recommend because it's a real PITA to maintain).

If you wanted to manage all your sql handles globally and are willing to ping the server before every transaction to see if the connection is good I suppose you could void all the handles, reconnect, and return them to the state they were in before the network took a crap. I think someone at ITG tried this some time ago - I don't know if they had any success with it.

[Adrian Whittell]

When you unplug the network the IP address stays the same (assigned by DHCP). Not sure how the IT guys have done that.
Also on my machine it seems to take > 10 seconds for the network to be unplugged and for the problem to occur.
Also the SQL Server IP address doesn't change either.
So I have a local machine where the IP address stays the same running a local SQL Server instance where the IP address stays the same after unplugging the network cable.
Yet the communication is still lost (So while one instance of CTD is saying it cannot communicate with the server) you can happily open a new instance and it will be fine.
I have also tried to trap the error and reconnect as if logging in again (to make the breakdown a little more bearable) but I actually get the same error (Communication Link Failure). I have to exit the app and go back in before it will recognise the SQL Server again.

The problem seems to be getting worse for some reason in the last year whereby our customers are finding our software almost unusable (We've had to go to various meetings to try and discuss a solution - Our stock answer has usally been fix your network - but after investigating they have found the same finding as us and want to know why it can't CONTINUE to work after just a brief glitch). I'm wondering if some PTF (some have suggested XP PTF 2) has made the problem worse. But I can't find an answer anywhere on this (even the Microsoft forums where others have the problem).

I've have reported this to Gupta support but haven't heard anything yet - Thought I may get some answers from others who've had similar problems.

Adrian
Tribal

[Adrian Whittell]

Can I also add that I have never seen it personally in 10 years working locally in a dev environment until someone told me to just to unlug the network cable to replivcate so our own network seems sound (So I'm alright ).

[Adrian Whittell]

Unify Support did contact me promptly as usual and the problem seems to be the limitation of using the SQL base API for all DBs including SQL Server(ODBC), Oracle etc.
It is not tolerant to breaks in the network.
You have to run sqldob and sqlini external functions declared in SQLWNTM.DLL
This resets all connections which has to be done before continuing.
I've tried this by trapping SAM_SqlError globally and although it works and does in fact reset, enabling us to log in again, the problem is that if there are any open cursors (at the current point in the app where the SQL failed), they will be closed by this reset so if you just continue you will get invalid cursor errors.
So it looks like the only solution is trapping every local bit if SQL with a When Error locally and reconnecting manually again all current cursors which were previously open (for us thousands of statements and therefore again not an option).

Looks like we'll have to wait for the re-write of the router in a future release.

Thanks for reading anyway and thanks to unify support for the quick response!

Adrian
Tribal

[Adrian Whittell]

Just done a bit more investigating and found this issue below with Microsoft Server 2003 and SQL Server.
It explains an awful lot about how some customers who have been happy for years have suddenly started getting this issue (to the point where our apps are almost unusable)
At some sites the problem happens every 3 or 4 minutes.

Anyway the article:-
http://support.microsoft.com/kb/942861

Adrian
Tribal