HOWTO: Reusing ADO Connections Within MTS Transactions
ID: Q234218
|
The information in this article applies to:
-
Microsoft Transaction Server 2.0
-
Microsoft Visual Studio 6.0 sp1, sp2, sp3
-
Microsoft Data Access Components version 2.1
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 rise an "Unspecified Error" [-2147467259 / 80004005] when executing another operations against the database.
MORE INFORMATION
When within a transaction in MTS, using an open ADO Connection object on multiple operations it is recommends the following:
- All open recordsets be either disconnected; or closed and set to nothing before executing other operations,
- All Command objects that are not used are set 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.
REFERENCES
For additional information about , please see the following
article(s) in the Microsoft Knowledge Base:
Q186342 HOWTO: Create a 3-Tier App using VB, MTS and SQL Server
Additional query words:
Keywords : kbMTS200 kbVS600 kbVS600sp2 kbVS600sp3 kbGrpCom
Version : WINDOWS:2.1,6.0 sp1, sp2, sp3; winnt:2.0
Platform : WINDOWS winnt
Issue type : kbhowto