March 19, 2007 at 7:31 am
Hello,
I am working on a server application (VB6) that communicates to other apps over LAN and uses SQLServer 2000 back end for direct database updates/data retrieval. The app uses ADO to communicate with the SQL server.
There are several database updates on different tables that I have to perform, and few must be executed as a transaction. (I tried to persuade our DBA to create a stored procedure for it, but he disagreed since we are in the process of a major database clean up). So for now I have to do it using VB code.
I wrap my dbConn.Execute dbUpdateStmt (which contains 1 insert and 1 update statement) in dbConn.BeginTrans and dbconn.CommitTrans (or dbConn.RollbackTrans -- if not both of the updates went through) statements. I tested it with valid data and data that generates errors (unique key constraint violation on one of the updates) -- it works great, but:
when the server starts, it creates a single connection and re-uses it (it accesses the back end at least twice a second while running). If there are any problems with the connection -- I attempt to recover, but if the errors were caused by the network failure, obviously, I cannot. In this case, I am closing the connection object and try to re-open it before start using it again. It works pretty well when NOT using Transaction processing. If the network happens to go down right after I called .BeginTrans (even if it comes back up later on), I cannot call RollBackTrans without generating and Error AND I cannot close a connection (so that I can try to re-connect) due to the fact that an oen Transaction is pending, and there is only one transaction allowed per connection. In my error handling procedures I do the following -- I destroy the connection object by setting it to nothing; I create a new one and re-initialize it; this does the trick -- server continues running, however, I have a couple of problems with this:
1. What happens to the connection at the back end when there is a network disconnect and connection has not been explicitely closed? Does it just sit there, or SQL Server disposes of it when such types of errors occur (link failure)?
2. Is there any better way to handle this situation?
3. Ideally, the users should be able to run this server for months without restarting it -- it must be operational 24/7, so if indeed there is a possibility of having "lost" connections "dangling" at the server site, is there any way to identify them and clean them up?
Thank you very much for your help
March 19, 2007 at 8:56 am
Can't help you with 1 or 3, other than to say that if you're using MTS I'm pretty sure that the connections are diposed are a time, but if you're going direct to the database you may have to kill an open SPID. But I will defer that to someone smarter than me on connections.
Regarding 2, I recommend you create a module level boolean with an initialization value of FALSE to indicate if a transaction is in progress. The connection object also has to be a module level variable. Instead of setting it after you begin trans, tie it to the connection events. You would have three blocks of events code for: BeginTrans Complete (sets flag to TRUE), CommitTrans Complete, and RollbackTrans Complete (both of which set the flag to FALSE). Your error handling needs to check the value of this flag and take the appropriate action. You may want to consider adding this check to the disconnect event. I don't do that, but it's something to consider.
Hope that helps.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply