Microsoft KB Archive/262944

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

Article ID: 262944

Article Last Modified on 6/12/2001

-

APPLIES TO


 * Microsoft Transaction Services 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

Keywords: kbdatabase kbprb KB262944

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.