PRB: Error -2147217900 When You Run Distributed Queries in SQL from MTS (260895)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft Transaction Server 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]: "Only one transaction can be active on this session"
-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.

REFERENCES

For additional information, see the "SET XACT_ABORT" topic in the SQL Server Books Online.
Modification Type:MajorLast Reviewed:10/28/2003
Keywords:kbDTC kbprb kbSQLProg KB260895
©2003 Microsoft Corporation. All rights reserved.