| Groups | Blog | Home |
sql server clients : A TOUGH ONE - audit Login, Audit Logout connection
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.
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 [quoted text, click to view] "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
[quoted text, click to view]
Phil Smith wrote: > 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. If you took the time to create the workaround of "select @@version", why don't you just change the code to create a connection object as necessary instead of relying on the global one?
It is not uncommon for the underlying connection methods (OLDEB, DAO etc)
to create additional connections when it feels it is necessary. I doubt that this is a contributing factor to your connectiosn being dropped. Have you looked at possible timeout problems running queries? This can cause a connection to be dropped. Rand This posting is provided "as is" with no warranties and confers no rights.
[quoted text, click to view]
"Phil Smith" wrote: <snip> [quoted text, click to view] > Our site was reporting errors which were attributed > to loss of client connection to the server. <snip> [quoted text, click to view] > we think what is happening is that ADO is just > creating additional connections <snip> Phil, As has already been pointed out, "hidden" connections can be created behind your back. The most common situation I know of with ADO is opening multiple recordsets on a connection object: if there's an open recordset on a connection, opening a second recordset creates a hidden connection behind the scenes. As for strange connection failures: we've observed one situation with ADO like this... if the physical Ethernet link is broken and then some kind of network operation on the Connection object is requested, an error will be thrown, the Connection's State property will still be adStateOpen, and any attempt to use the Connection object will fail. Even after the physical link is re-established, the connection object will no longer work (even though the State property shows it as open). We found this out because one of our main Ethernet switches was not on a UPS and on a outlet not connected to the backup generator and main UPS (I know, I know, don't get me started). In any event, when that switch lost and regained power, anyone connected to the switch with an ADO connection was broken (assuming they'd been doing anything). We were able to reproduce this for testing purposes by opening a connection, removing the Ethernet cable on a computer, issuing a simple select on the Connection object, and then re-connecting the Ethernet cable. Our applications use a wrapper class around the ADO connection, so our solution was to detect this particular state when a database operation raised an error. If we found the state had occurred, we would save the connection string, close the connection, destroy it, create a new connection, and open the new connection with the same connection string. I'm leaving out retry logic and our application-level transaction tracking, but you get the idea... Here's the VB 6 code we used for detecting this (editing in my news editor, so not guaranteed to be syntax error free): Private Function LinkFail(ByVal conn As Connection) As Boolean Dim e As ADODB.Error Dim bLinkFail As Boolean If conn Is Nothing Then bLinkFail = False ElseIf conn.State <> adStateOpen Then bLinkFail = False Else For Each e In conn.Errors 'Not the cleanest thing in the world, but it works If Trim$(UCase$(e.SQLState)) = "08S01" Then bLinkFail = True Exit For End If Next e End If LinkFail = bLinkFail End Function Craig
Don't see what you're looking for? Try a search.
|
March 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |