Re: Reconnect does not work TADOconnection

If you want to see the code we used, let me know.

-Steve-

"Ron J" <rjohnson@xxxxxxxxxxxx> wrote in message
news:47cd8336@xxxxxxxxxxxxxxxxxxxxxxxxx
Thanks Steve. 'ping' sounds like a good idea - I'll try it.

Funny thing for us is we have had this application on SQL2000 for 4 years
(400 users) and 1 or 2 of these drops per week. We upgraded the servers
(to Core2 x 2, SAN, SQL2005, 64bit W2K3) and we get about 100 per day (out
of 400 users). Some users are remote over T1 so they are really hot when
they have to restart the app (load time is 90 seconds over T1). [We are
porting to Web now]

On your timed ping, how often are you pinging the server to keep it
active? What kind of 'ping'? Just any simple short Select to do something?

Thanks,
Ron



"Steve Zimmelman" <skz@xxxxxxxxxxxxxxxxxx> wrote in message
news:47cc21a6$1@xxxxxxxxxxxxxxxxxxxxxxxxx
Hi Ron,

We've dealt with similar issues using ADO and SQL Server. When the
connection from SQL is dropped, the ADOConnection remains active until
you attempt to read or write using it. You may already know this, but
when you close the connection it also closes any open ADOdataset's that
are bound to that connection. So you'll need open and reestablish all
the datasets.

I don't know if stopping the SQL service is a fair test though. In our
experience, the connection is usually dropped due to inactivity. But we
were never able to figure out why, or how much inactivity caused SQL to
drop the connection. We ended up subclassing the TAdoConnection and
added a timed "ping" to server. This solved 99% of the dropped
connection errors that were plaguing us.

HTH,
-Steve-


"Ron J" <rjohnson@xxxxxxxxxxxx> wrote in message
news:47c75343$1@xxxxxxxxxxxxxxxxxxxxxxxxx
Trying to handle network errors or communication errors returned to our
TADOconnection. Using an exception handler something like this:

if (E.message = 'communication link failure') then
begin
Conn1.Close;
conn1.open;
end;

To get the error I just turn off SQL server service and try to do a
write in the applicaton. The interesting thing is when the error happens
the connection is still open, so I force a close, then re open it.
However, at that point (with SQL server turned back on), the application
does not recover. It keeps getting the exception and DB operations do
not work.

Any ideas?







.

Relevant Pages

  • Re: Error "SQL Server does not allow remote connections"
    ... The application could not connect to the sql server db. ... network is blocking the connection. ... SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 ... integratedSecurity, SqlConnection owningObject) +737554 ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Connection from remote computer to network SQL Server
    ... There is no firewall on the W2K machine acting as the SQL server. ... I tried making the SQL machine a "trusted" on the router. ... connection works. ... To find the IP address of your computer inside the network, ...
    (microsoft.public.access.adp.sqlserver)
  • ADO.net Orcas Samples Install Problem
    ... An error has occurred while establishing a connection to the server. ... When connecting to SQL Server 2005, this failure may be caused by the ... SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, ... or am I better off with a full SQL Server install. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: OpenDataSource SQL Server xpress problem
    ... I tried your suggestions with some success. ... I tried setting up the .odc file through the Word Mail Merge wizard as ... If I work through the dialog to connect and then click the 'Test Connection' ... but I suspect it's because until recently most SQL Server ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Exception trying to import data from Excel .
    ... An attempt to install Microsoft SQL Server 2005 this week has not gone ... The connection type "EXCEL" specified for connection manager ... to create a connection manager for an unknown connection type. ...
    (microsoft.public.sqlserver.tools)