Article ID: 306649 - View products that this article applies to.
This article was previously published under Q306649
Microsoft OLE DB Provider for SQL Server does not support nested transactions, and the following error message is returned:
Microsoft SQL Server also returns the following error message if the provider is a SQL Server-specific provider:
Could not start a transaction for OLE DB provider '%ls'.
Server: Msg 7392, Level 16, State 2,
Could not start a transaction for OLE DB provider 'SQLOLEDB'. [OLE/DB provider returned message: Only one transaction can be active on this session.]
This error indicates that a data modification statement is being attempted against an OLE DB provider when the connection is in an explicit or implicit transaction, and the OLE DB Provider does not support nested transactions. SQL Server requires this support so that, on certain error conditions, it can terminate the effects of the data modification statement while continuing with the transaction.
To work around this problem, set XACT_ABORT to ON before the transaction. This causes SQL Server to terminate the surrounding transaction when an error occurs while processing the data modification statement. If SET XACT_ABORT is ON, SQL Server does not require nested transaction support from the OLE DB Provider.
Steps to Reproduce BehaviorThis sample uses a linked server, which uses OLE DB Provider for SQL Server, to explain the effect of XACT_ABORT.
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/177138/EN-US/ )INFO: Nested Transactions Not Available in ODBC/OLE DB/ADO
(http://support.microsoft.com/kb/187289/EN-US/ )HOWTO: Implement Nested Transactions with Oracle
Article ID: 306649 - Last Review: December 9, 2005 - Revision: 4.2