Re: detecting a dead connection

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Brannon Jones (brannonjNOSPAM_at_gmail.com)
Date: 09/20/04 

Date: Mon, 20 Sep 2004 13:32:09 -0700

Hmm, I think that the SQL driver must cache the state of the connection
after the first check. Sounds like a bug.

But I don't think you should use the SQL_ATTR_CONNECTION_DEAD attribute at
all (or SQL_COPT_SS_CONNECTION_DEAD). You need to have code that can handle
disconnect errors anyway, so why have a special check? Also, the bigger
problem is, what happens if you detect that the connection is alive, and
then try to use it, and during that brief period of time, the connection
dies? If you don't handle that case, then your app will experience random
weird behavior. However, if you correctly handle that case, then you don't
even NEED to detect if the connection is alive or not.

Brannon

"Gideon" <grashkes@yahoo.com> wrote in message
news:fb0829e1.0409181058.74858989@posting.google.com...
> Hi Brannon, and thanks for you reply.
> I've done some more research and here's what I came up with.
> Lets focus for now solely on SQLGetConnectAttr.
> I need to use it to find out whether the connection is alive or not
> before I attempt to use it, as I want to refrain from using exception
> handling in this case.
> I've found out that on SQL Server 2000, the const
> 'SQL_COPT_SS_CONNECTION_DEAD' has a different value than
> 'SQL_ATTR_CONNECTION_DEAD' (1244 and 1209, respectively).
> However, I am still experiencing some strange behaviour.
> Using SQLGetConnectAttr with 'SQL_COPT_SS_CONNECTION_DEAD' on a dead
> SQL Server connection (i.e. using a SQL Server 2000 driver) I can
> detect that the connection is dead. After reconnecting however, the
> function does not detect that the connection is running again, and
> says that the connection is dead still.
> As my system needs to work with Oracle and DB2 connections as well,
> things get messier.
> With DB2, there's no problem, other than I have to use the old const
> 'SQL_ATTR_CONNECTION_DEAD' to make it work.
> With the Oracle driver, neither seem to work.
>
> To sum up:
> On SQL Server - I cannot detect that the connection is running again.
> On DB2 - fine.
> On Oracle - the function always says the connection alive.
>
> Thanks for your help,
> Gideon.
> "Brannon Jones" <brannonjNOSPAM@gmail.com> wrote in message
news:<#mv$brOnEHA.1412@tk2msftngp13.phx.gbl>...
> > I'm curious what problem you've heard exists with
SQL_ATTR_CONNECTION_DEAD?
> >
> > Why do you need to test whether or not the connection is dead? Just try
to
> > use it and have the correct error handling in place.
> >
> > What happens if you successfully test that the connection is alive, and
then
> > it dies a second later? You have to handle that error case anyway, so
why
> > worry about testing to see if it's dead or not?
> >
> > Brannon
> >

Relevant Pages

  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... SQL CE/Mobile has a background thread that flushes the changes ... they run for long hours and we have not seen memory leaks so far. ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... So it is a kind of asynchronous commit which happens every 10 ... I am not sure of this claim as we have many applications built on SQL CE ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • Access 2003 adp/proxy security - A substitute for SYSTEM_USER()
    ... We need a way for SQL to know the nt username that initiated the proxy ... Many stored procedures and views use a UDF that also depends on ... SyUserLogin and use HostIDand HostName() to find the right row. ... We've played with the Connection dialog settings, ...
    (microsoft.public.access.security)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)
  • Access 2003 adp/proxy security - A substitute for SYSTEM_USER()
    ... We need a way for SQL to know the nt username that initiated the proxy ... Many stored procedures and views use a UDF that also depends on ... SyUserLogin and use HostIDand HostName() to find the right row. ... We've played with the Connection dialog settings, ...
    (microsoft.public.sqlserver.connect)