Error 3989 or 3988 when a distributed query that joins multiple tables is hosted by remote instances of SQL Server

Applies to: SQL Server 2014 Business IntelligenceSQL Server 2014 Business IntelligenceSQL Server 2014 Developer

Symptoms


When a distributed query that joins multiple tables and is hosted by remote instances of Microsoft SQL Server is executed while the XACT_ABORT session setting is ON, you may encounter one of the following errors: 
Msg 3989, Level 16, State 1, Line #

New request is not allowed to start because it should come with valid transaction descriptor.


Msg 3988, Level 16, State 1, Line #

New transaction is not allowed because there are other threads running in the session

Cause


There are some design limitations in the way SQL Server handles distributed queries (DQs) when the following conditions are true: 
  • SQL Server joins multiple tables of one remote SQL Server data source.
  • The session that is issuing the query is not enlisted in a distributed transaction. 
In this situation, an attempt to run the query may raise either of the two errors that are mentioned in the "Symptoms" section.

Workaround


To work around the issue, enclose the distributed query in a "begin distributed transaction" statement: 
BEGIN DISTRIBUTED TRANSACTION  <Distributed Query> COMMIT TRANSACTION