Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How To Reuse ADO Connections Within MTS Transactions


View products that this article applies to.

This article was previously published under Q234218

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


References

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
186342� How To Create a 3-Tier App using VB, MTS and SQL Server

↑ Back to the top


Keywords: KB234218, kbhowto

↑ Back to the top

Article Info
Article ID : 234218
Revision : 3
Created on : 8/30/2004
Published on : 8/30/2004
Exists online : False
Views : 457