PRB: Transaction Does Not Flow When You Use MTS Components from SQL Server Stored Procedure (262944)


The information in this article applies to:

  • Microsoft Transaction Server 2.0
This article was previously published under Q262944

SYMPTOMS

When you use the sp_OA system procedures to create an object from a SQL Server stored procedure, the object does not inherit any transaction under which the stored procedure is running. This problem occurs even if you configure the object in COM+/MTS (Microsoft Transaction Server) to support transactions.

MORE INFORMATION

You can use the sp_OA stored procedures (such as sp_OACreate and sp_OAMethod) to create and use a COM object from Transact-SQL commands in SQL server stored procedures. However, the transaction does not flow to the created object.

For additional information about sp_OA stored procedures, click the article numbers below to view the articles in the Microsoft Knowledge Base:

180780 INF: How Sp_OA Procedures Extension to SQL Server Is Implemented

152801 INF: Examples of Sp_OA Procedure Use and SQLOLE.Transfer Object

Steps to Reproduce Behavior

  1. Write a stored procedure that invokes a COM+/MTS component named Component A, and then write a method in Component A that checks the GetObjectContext.IsInTransaction method.
  2. Write another COM+/MTS component, named Component B, that invokes the stored procedure that creates and invokes a method on Component A.
  3. On the Action menu of Component A, click Properties. On the Transaction tab, set the transaction property to Supports Transactions.
  4. On the Action menu of Component B, click Properties. On the Transaction tab, set the transaction property to Requires a Transaction.
  5. Write a simple client that creates and invokes the method on Component B, which invokes the stored procedure.
  6. In Component A, GetObjectContext.IsInTransaction returns FALSE, which indicates that the transaction does not flow.
The GetObjectContext.IsInTransaction method also returns FALSE if you put the call to Component A in a BEGIN DISTRIBUTED TRAN and COMMIT block inside the stored procedure.

In Microsoft Windows 2000, Bring Your Own Transaction (BYOT) offers similar functionality. BYOT allows you to create a COM+ component with an external transaction. For more information on BYOT, see the following Microsoft Developer Network (MSDN) article:

http://msdn.microsoft.com/library/psdk/cossdk/pgdtcintro_5pi7.htm

Modification Type:MajorLast Reviewed:6/12/2001
Keywords:kbDatabase kbDSupport kbprb KB262944
©2002 Microsoft Corporation. All rights reserved.