Microsoft KB Archive/234218

= How To Reuse ADO Connections Within MTS Transactions =

Article ID: 234218

Article Last Modified on 8/30/2004

-

APPLIES TO


 * Microsoft Transaction Services 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q234218



SUMMARY
When you use a single ADODB Connection object multiple times within an MTS transaction, other ADO objects must be cleaned up after use. Also, client cursors should be used when possible. If ADO objects are not cleaned up properly, ADO may raise an "Unspecified Error" [-2147467259 / 80004005] when it runs other operations against the database.



MORE INFORMATION
When you use an open ADO Connection object on multiple operations within an MTS transaction, Microsoft recommends that you follow these steps:
 * Either disconnect all open recordsets, or close and set all recordsets to nothing before executing other operations.
 * Set all Command objects that are not used to nothing.

If these steps are not followed, ADO might display an "Unspecified Error" or open secondary connections to complete the operations.

This situation arises more frequently when within an MTS transaction.

A simple example of code that FAILS when it is run within an MTS transaction as follows:

This is FAILING code: Public Sub DoStuff On Error Goto ErrHandler

Dim oConn As New ADODB.Connection Dim oCmd As New ADODB.Command Dim oRS As ADODB.Recordset

oConn.Open sConnectionString

Set oCmd.ActiveConnection = oConn oCmd.CommandText = "SELECT * FROM Authors" oCmd.CommandType = adCmdText Set oRS= oCmd.Execute '...Operate on Recordset...

'This FAILS if executed within an MTS transaction with ADO 'Unspecified   Error' message: oConn.Execute "INSERT INTO Jobs (job_desc, min_lvl, max_lvl) values (   'edjez',25,100 )"

GetObjectContext.SetComplete

Exit Sub ErrHandler: GetObjectContext.SetAbort Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile,   Err.HelpContext End Sub The preceding code should be optimized and organized as follows: Public Sub DoStuff On Error Goto ErrHandler Dim oConn As ADODB.Connection Dim oCmd As ADODB.Command Dim oRS As ADODB.Recordset

Set oConn = New ADODB.Connection oConn.Open sConnectionString 'Here we specify cursor locations to adUseClient (3) 'because we will be reusing the connection afterwards

Set oCmd = New ADODB.Command Set oCmd.ActiveConnection = oConn oCmd.CommandText = "SELECT * FROM Authors" oCmd.CommandType = adCmdText

Set oRS= oCmd.Execute '...Operate on Recordset... '...and we won't be needing it anymore so let's clean up   oRS.Close Set oRS = Nothing Set oCmd = Nothing

oConn.Execute "INSERT INTO Jobs (job_desc, min_lvl, max_lvl) values (   'edjez',25,100 )"

oConn.Close Set oConn = Nothing

GetObjectContext.SetComplete Exit Sub ErrHandler: GetObjectContext.SetAbort Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile,   Err.HelpContext End Sub Please note that the error is raised to the caller within the error handler, and that the ADO objects are not created using ObjectContext.CreateInstance but rather the New operators - this is because ADO objects do not need to be created using ObjectContext.CreateInstance to participate in the current transaction (if any). If you are using a custom wrapper for database access (which in turn could be using ADO) you would need to create this wrapper using ObjectContext.CreateInstance for it to participate in the MTS transactions.

