A while back, I ranted that the design and implementation of Transact-SQL should be guided by a prime directive that guarantees no interference with the flow of set-based data in Transact-SQL.
That was primarily motivated by the fact that no such guarantee exists today in T-SQL. That is, when you move set-based data around in T-SQL, you may find it stopped dead in tracks for no apparent or meaningful reason.
Recently, I’ve run into another example of not being able to move set-based data around in T-SQL, and here is how to reproduce it.
You need to have three SQL Server 2008 instances, and let’s assume they are SQLA, SQLB, and SQLC.
On SQLB, create a linked server to SQLC, and create the following stored procedure:
create proc p_testB
create table #tmp(i int)
select * from #tmp
On SQLC, create a linked server to SQLB, and create another stored procedure:
create proc p_testC
create table #tmp(i int);
from openquery(SQLB, 'select top 1 id from sysobjects')
select * from #tmp
Now, if you want to access the resultset of p_testB from SQLA, you naturally expect to be able to do a remote proc call on SQLA as follows (assuming that linked server to SQLB is already created):
The only problem is that the above call does not work, and you would get the following error message:
Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.
Msg 1206, Level 18, State 199, Procedure p_testB, Line 4
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the
Now, strictly speaking and to some extent, this is not a T-SQL issue per se. Rather, it has something to do with how SQL Server 2008 handles loopback calls and the implementation of MSDTC.
However, the point is that it does not matter what the underlying root cause for this problem is. What matters is that you have no guarantee that you can move set-based data around in T-SQL, and that is a fundamental problem. If the design and implementation of T-SQL had been guided by a prime directive such as stated earlier, guaranteeing the flow of set-based data would have taken precedence and whatever the underlying limitations with MSDTC (or changes with how loopback calls are handled or problems with any underlying supporting technologies) may be, they would have been fixed/modified/replaced in order to guarantee the set-based data flow.