FIX: MTS COM Object Fails to Pool OLE DB Connections

This article requires a good knowledge of Component Object Model (COM), Microsoft Transaction Server (MTS), and any language to build COM components like Microsoft Visual Basic or Visual C.

Multiple connections accrue on a SQL Server database or other databases as Component Object Model (COM) objects are called using Requires New transaction support.

The Microsoft Transaction Server (MTS) resource manager fails to clean up the pooled connection status when the SetComplete function is called. Therefore, connections used in Requires New transactions are not reused.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This bug has been fixed in Microsoft Data Access Components (MDAC) 2.6.

Steps to Reproduce the Behavior

The scenario for this bug to occur is very specific.

1. Set up three COM objects in MTS.
2. In MTS, mark your three COM components as follows:
   The first object (obj1): Requires transactions
   The second object (obj2): Requires new transactions
   The third object (obj3): Supports transactions
   
3. Have the client application call a method on obj1. The method in obj1 has a loop. In the loop the method in object1 calls a method of obj2 that connects with a database using an OLEDB provider (SQLOLEDB or MSDAORA), disconnects from the database, and then calls the SetComplete function. The method of obj2 connects a second time with the same database by using the same connection string, disconnects from the database, calls SetComplete, and then returns to obj1.
   
   Now, obj1 calls a method of obj3. This method in obj3 connects with the same database by using the same connection string used in obj2. The method in obj3 then disconnects, calls SetComplete, and returns to obj1.
   
   Next, obj1 calls SetComplete and continues its loop.
   
   NOTE: Make sure that the session pooling service is not disabled for your OLE DB provider before you set up your components in MTS.
   
   The end result is that the connections are entered into the session pool but they are not reused. To see this, use Perfmon and note the increasing spike when you watch the number of user connections on SQL Server.

For more information, refer to the Microsoft Developer Network (MSDN) documentation.