question

Is it possible to perform a cross instance update through a linkedserver without escalating to a msdtc handled distributed transaction? The two instances are on the same physical server.

According to trusted source mrdenny, in this article, msdtc should not be enlisted if the two instances are on the same physical box, though in my tests the cross-instance transaction always escalates to a msdtc handled one.

Is there a way to control the escalation? My setup involves two instances of SQL 2005 running on the same physical Server 2003 box. Ive created a linked server from ServerA to ServerB, run a simple cross-instance update statement using 4 part naming, and then inspect the 'Transaction List' viewer in DTC. I also see it listed as distributed using this query:

select  s.text,                    
        case dtst.is_local when 0 then 'DISTRIBUTED' else 'LOCAL' end [txScope],                    
        dtst.is_enlisted [txEnlisted]                    
from    sys.dm_tran_session_transactions dtst                    
join    sys.sysprocesses sp on                    
        dtst.session_id = sp.spid                    
cross                    
apply   ::fn_get_sql(sp.sql_handle) s;