Connection Failure
Peter Tickler
Running Delphi 6, connecting to three separate SQL Server 8.0 DBs on two servers.
The application normally runs fine, but from time to time when a user opens a Customer record (this involves opening several queries), he/she gets an error "Connection failure" or "[DBNETLIB][ConnectionWrite(WrapperWrite())] General network error". The user then has to close the application down, reopen it and it works fine.
Any ideas on what might be the cause, or how to trap/circumvent it?
Steve Zimmelman
seems when the connection is severed for any reason, the SQL DB handle becomes
invalid. ADO won't know about it until the connection is used. Then it raises
the exception. We very rarely saw it on the internal network, but occasionally
when there no activity from an ADOConnection, it seems that SQL releases the
connection handle, causing the same problem.
If you trap this specific error, and close and open the ADoConnection you
shouldn't have to restart the app.
ADOConnection.Close ;
ADOConnection.Open ;
-Steve-
"Peter Tickler" <ptic...@potato.org.uk> wrote in message
news:448e8b87$1...@newsgroups.borland.com...
Jason Fischer
I am getting something similar.
I try to open a query and get the Connection Failure message.
But I can get this to happen on one DB and not another.
Both DB's are on the same server. The one it always happens on and the other
it never happens on.
Any ideas as to what could be causing this?
"Steve Zimmelman" <s...@charter.nospam.net> wrote in message
news:4496cf05$1...@newsgroups.borland.com...
Steve Zimmelman
I posted this sometime last year...
***************************
I've been using ADO accessing MS SQL Server for a few years. It seems there
is a problem with connection failures if the connection isn't used for a
period of time. Using a VPN I've seen this problem occur with only 15
minutes of inactivity.
In case anyone has experienced this, here's how I resolved it.
I subclassed the TADOConnection component and added a TTimer object. I
exposed the timer's interval for flexibility, but the default interval is
set to 300000 (5 minutes). The OnTimer event executes an inert stored
procedure, similar to a ping, just to keep the connection going.
Procedure TskzADOConnAlive.InternalOnTimer(Sender:TObject);
Var i : Integer ;
Begin
FTimer.Enabled := False ;
Execute('Exec sp_server_info 1',i,[eoExecuteNoRecords]);
FTimer.Enabled := True ;
End;
The timer is enabled after the connection is established, and disabled after
the connection is closed.
To make sure that the timer event only executes when the connection has been
idle for a period of time, I hooked into the OnWillExecute event. Something
like this:
Procedure TskzADOConnAlive.InternalOnExecute(Connection: TADOConnection;
var CommandText: WideString; var CursorType: TCursorType;
var LockType: TADOLockType; var CommandType: TCommandType;
var ExecuteOptions: TExecuteOptions; var EventStatus:
TEventStatus;
const Command: _Command; const Recordset: _Recordset);
Begin
If FTimer.Enabled Then Begin
FTimer.Enabled := False ;
If FKeepAlive And (Not (csDesigning In ComponentState)) Then Begin
FTimer.Enabled := True ;
End;
End;
End;
This seems to work without interfering with the application. I had an app
up with a remote connection via a VPN all day yesterday, and not one
connection failure even with several hours of inactivity.
****************************
http://www.skzimmelman.com/Articles/skzADOConnKeepAlive.html
-Steve-
"Jason Fischer" <jason_fi...@unwired.com.au> wrote in message
news:4497...@newsgroups.borland.com...
Peter Tickler
I have decided to try the same approach, using a timer and running a query every 2 minutes which returns no data but hopefully maintains the connection.
It will be interesting to see what happens.
