Re: A TOUGH ONE - audit Login, Audit Logout connection

From: Ilya Margolin (ilya_at_unapen.com)
Date: 04/28/04 

Date: Wed, 28 Apr 2004 09:26:46 -0400

Phil,

First off you can use <ADODBConnection object>.Status = adStatusOpen.
<ADODBRecordSet object>.Status will give status of a recordset, not of a
connection. Second, we've never experienced ADO dropping connection by
itself for no good reason. A connection will be dropped in only two cases:
you explicitly terminate connection or connection object goes out of your
code scope. So, dig through the code again...

Ilya

"Phil Smith" <phil.smith@informatics.co.uk> wrote in message
news:4bbacf17.0404280425.71cc0a84@posting.google.com...
> Hi all:
>
>
> Hope you can shed some light on this. we have a Vb6 app connecting to
> a slq server database using ado 2.6. when each client is started they
> initialize a connection object which is kept open for the life cycle
> of the client (i know this not recommended but it is an inherited
> problem).
>
> Our site was reporting errors which were attributed to loss of client
> connection to the server. As we still haven't found a clear way of
> alerting the client when it has loosed its connection to the server
> ((1)if anyone has a suggestion let me know)- the Recordset.state
> property and Recordset events are useless for this.
>
> Our solution for this was - when ever we pass the connection object
> to a recorded, internal to the connection object we test the
> connection to the server by executing a "Select @@Version" on the
> server using a prepared command object. if this test fails then we try
> re-establish the connection , which is looped on the
> connection.timeout. so the user has the choice to reconnect or exit
> the app.
>
> now running a trace using profiler with audit
> login/logout,ClentProcessID, spid and the other main event, when the
> app starts up it gets its unique ClentProcessID & spid from sql, so we
> can identify the app and its connection (audit login) at intensive
> parts of the code for some unknown reason an additional spid connects
> for the same ClentProcessID and this can carry on for up to 5 layers
> so what the trace looks like is....
>
> ClientProccessID|SPID|EventClass|
> ---------------------------------
> 444 | 51 |SQL:Batchcomplete
> 444 | 51 |SQL:Batchcomplete
> 444 | 51 |SQL:Batchcomplete
> 444 | 52 |Audit Login
> 444 | 52 |SQL:Batchcomplete
> 444 | 52 |Audit Out
> 444 | 52 |Audit Login
> 444 | 52 |SQL:Batchcomplete
> 444 | 53 |Audit Login
> 444 | 53 |SQL:Batchcomplete
> 444 | 53 |Audit Out
> 444 | 52 |Audit Out
> 444 | 51 |SQL:Batchcomplete
> 444 | 51 |SQL:Batchcomplete
> 444 | 51 |SQL:Batchcomplete
>
> you can see these are nested and can be upto 5 levels deep. (2) we
> think what is happening is that ADO is just creating additional
> connections at times of have traffic without us knowing which may be
> contributing to our loss off connection, is there any way to further
> track this or clarify this.
>
>
> we don't think it is a connection pooling problem as this has been
> switch off, and we have taken into account spid's for other apps
> connected to the server.
>
> I hope you have enough info. so if anyone has a solution to (1) or can
> confirm (2) it would be much appreciated.
>
>
> Gratefully - a puzzled one

Relevant Pages

  • Re: Deployment + Vista
    ... I'm talking about the user being able to edit the server connection setting ... MSI will allow you to edit the app directory, ... function in order to put together the connection string something like: ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Digging in to trace files
    ... > Ed you are probably aware that even though the server is running MTS ... > you can create a dedicated connection when you need to correct? ... However, the app uses ... to shared server by requiring a high number of concurrent sessions ALSO ...
    (comp.databases.oracle.server)
  • Re: Thread Issue
    ... > in/output streams to all of them from the application server. ... > a sepreat client. ... So your 'app' server is acting as a proxy; every client connection made to ...
    (comp.lang.java.programmer)
  • Re: Interesting issue connecting to Sql server through a firewall..
    ... Client Network Utility. ... >This app communicates to a sql 2000 server on our corporate network. ... >connection is via microsofts jdbc 2000 connector. ...
    (microsoft.public.sqlserver.security)
  • Re: Opening ServerSocket in EJB
    ... However, there's a vague chance it will work, and if it does, i imagine opening a server socket right from an EJB will be the simplest way of solving your problem. ... I'm not aware of a standard, portable way of adding application-layer protocols to app servers. ... You'd write a JCA resource adaptor to handle the connection, configure an instance of it in your app server, and use that from an EJB, just as you'd use a database connection or message queue. ...
    (comp.lang.java.programmer)