THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

The Transact-SQL Prime Directive – a bad example

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:


use master
create proc p_testB
create table #tmp(i int)
insert #tmp
EXEC SQLC.master.dbo.p_testC
select * from #tmp

On SQLC, create a linked server to SQLB, and create another stored procedure:


use master
create proc p_testC
create table #tmp(i int);
insert #tmp
select *
  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):


EXEC SQLB.master.dbo.p_testB;


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
distributed transaction.


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.

Published Friday, December 18, 2009 1:12 PM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Piotr Rodak said:

Hi Linchi,

I wrote a post about a DATA ACCESS setting on local server some time ago

I just wonder if the scenario you described would work if you enabled DATA ACCESS locally on SQLB.

December 18, 2009 7:24 PM

Stephen Munson said:

I can appreciate the fact that you don't like having run into a limitation, but it would be an awful lot easier to decide whether this issue is even worth a comment if there was some kind of linkage between the issue and a real-world scenario in which you encountered the problem.  Without that kind of linkage, this sounds more like some odd-ball, arcane, rarely used scenario, where somebody probably didn't think their implementation all the way through.

Context, in this case, is golden...


December 19, 2009 10:02 PM

Leave a Comment


About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement