Microsoft KB Archive/260895

= PRB: Error -2147217900 When You Run Distributed Queries in SQL from MTS =

Article ID: 260895

Article Last Modified on 10/28/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft Transaction Services 2.0

-



This article was previously published under Q260895



SYMPTOMS
When you use the linked server in SQL Server to execute a stored procedure from Microsoft Transaction Server (MTS), you may receive the following error message:

[OLE/DB provider returned message]: &quot;Only one transaction can be active on this session&quot;

-2147217900 : OLE DB provider for SQL server



CAUSE
When a query is executed from the source SQL Server to a destination SQL Server, the destination SQL Server tries to create a local transaction. During the time of creation, the destination SQL Server checks to see if a transaction already exists and whether it can be part of it; as a result, you may encounter the above-mentioned error message.



RESOLUTION
To work around this error, call SET XACT_ABORT ON at the beginning of the stored procedure. When SET XACT_ABORT is turned ON, the entire transaction is terminated and rolled back.

