Error message when you implement a nested transaction with the OLE DB Provider for SQL Server: "Could not start a transaction for OLE DB provider"

Article translations Article translations
Article ID: 306649 - View products that this article applies to.
This article was previously published under Q306649
Expand all | Collapse all

On This Page

SYMPTOMS

Microsoft OLE DB Provider for SQL Server does not support nested transactions, and the following error message is returned:
Could not start a transaction for OLE DB provider '%ls'.
Microsoft SQL Server also returns the following error message if the provider is a SQL Server-specific provider:
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.]

CAUSE

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.

RESOLUTION

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.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

This sample uses a linked server, which uses OLE DB Provider for SQL Server, to explain the effect of XACT_ABORT.
  1. Use OLE DB Provider for SQL Server to create a linked server named Sales that uses Microsoft OLE DB Provider for SQL Server.
    USE Master
    GO
    EXEC sp_addlinkedserver 
        'Sales',
        N'SQL Server'
    GO
    					
  2. Open Query Analyzer, and run the following command.
    Begin Transaction
    Insert into Sales.Northwind.DBO.Customers(CustomerId,CompanyName) Values ('1000','Test')
    Commit Transaction
    					
    You receive the above-mentioned error message.

    Workaround

  3. Run the following command to set XACT_ABORT to ON:
    Set XACT_ABORT ON
    					
  4. Run the Insert statement again. Notice that the data is inserted into the table without an error.

REFERENCES

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
177138 INFO: Nested Transactions Not Available in ODBC/OLE DB/ADO
187289 HOWTO: Implement Nested Transactions with Oracle

Properties

Article ID: 306649 - Last Review: December 9, 2005 - Revision: 4.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.6 Service Pack 1
Keywords: 
kbprb KB306649

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com