How do I accurately determine if I've lost connection to SQL Server?

[Martin Binder]

Hi, I have an application that uses ADO to update a SQL table in MS SQL
Server. Here are the details of my setup:

Delphi 5, with Update Pack 1 and the ADO Express update
MS SQL 2000 client and server
MDAC 2.6

I am trying to trap the condition where I lose connection to the server.
When a query is fired and there is no connection to the server, an
EOLEException is raised. Looking at this exception as an EOLEException, the
ErrorCode is -2147467259, which according to this MS KB article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q168354

is an "Unspecified error". So I figured, OK, every time I get an
"unspecified error", I'll just assume I've lost connection to the server and
handle it. Well, unfortunately other simple SQL errors give me this
"unspecified error" code. Like if a column doesn't exist in the table, I
get that error code. I'm assuming other simple SQL errors like this
generate the same error code.

I've tried looking at the Errors property on the TADOConnection. When the
first query fails, the Errors property looks like this:

NativeError=0
Description='Connection failure'

Subsequent attempts to open and close the TADOConnection yield the
following:

NativeError=17
Description='[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not
exist or access denied.'

Although the second exception is giving me a valid NativeError, I really
need to trap the initial 'Connection failure', which has a NativeError of
zero.

So how can I accurately tell that I've lost connection to the server? Any
help on this will be greatly appreciated. Thanks!

Martin Binder

[Mark]

Try checking the TADOConnection state property.

"Martin Binder" <mbi...@gatewayticketing.com> wrote in message
news:3cc5adea$1_1@dnews...

[Martin Binder]

"Mark" <x...@y.com> wrote in message news:3cc60480_1@dnews...

> Try checking the TADOConnection state property.

When the initial exception occurs (connect fail), the State property of my
ADOConnection has a value of stOpen. On subsequent exceptions the, State
property gets changed to stClosed, but like I said before, I really need to
be detecting the lost connection on the first exception. Thanks for the
suggestion...

Martin

[Ian Stuart]

"Martin Binder" <mbi...@gatewayticketing.com> wrote in message
news:3cc5adea$1_1@dnews...

> I am trying to trap the condition where I lose connection to the server.

> When a query is fired and there is no connection to the server, an
> EOLEException is raised. Looking at this exception as an EOLEException,
the
> ErrorCode is -2147467259, which according to this MS KB article:
>

There is no way (I know of) to detect a connection failure with ADO without
querying the server. Rather than assuming the connection is OK
(TADOConnection.Connected = true) on critical updates, I actually test the
connection first using a command that reduces the possible errors you can
receive and has permissions for all users e.g.

function PingConnection(AConnection: TADOConnection; var err: string):
Boolean;
//var
// A: _Recordset;
begin
Result := false;
err := '';
if AConnection.Connected then
begin
try
//A := AConnection.Execute('EXEC sp_server_info 1', cmdText);
Result := true;
except
On E: EOleException do
...
end;
end else
err := 'Not connected';
end;

If your connection has dropped (tcpip or named pipe timeout, server
shutdown, hardware problem etc) then you will most likely get the
E.ErrorCode = -2147467259. There are only a few possible codes that can be
returned by this command. I also use this function to ping the server every
hour to prevent tcpip connections from being dropped after 2 hours
(default).

Regards
Ian

[Martin Binder]

"Ian Stuart" <ian.s...@chello.at> wrote in message
news:3cc6b85f_1@dnews...

> If your connection has dropped (tcpip or named pipe timeout, server
> shutdown, hardware problem etc) then you will most likely get the
> E.ErrorCode = -2147467259. There are only a few possible codes that can be
> returned by this command.

Thanks for the input. I think I'm going to end up checking for the
'unspecified error" code (-2147467259) and the "catastrophic error" code
(2147418113) in the errors collection on the ADOConnection to determine if
I've lost the connection. That seems to work for all of my test cases.

Martin