MS SQL "Connection Failure" Possible Solution

[Steve Zimmelman]

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

HTH,
-Steve-