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;