I have recently encountered a major problem with MSDTC service .
The MSDTC error only happen when we used the explicit transaction. If
remove the explicit transaction, it's work.
From SQLServer 1, when execute the Begin transaction statement
to linked server SQLServer 2.
DBCC TRACEON (7300,3604)
begin tran test_calculate_mix
select * from [SQLServer2].dev.dbo.productmix where mix_category = 'ABC'
if @@error != 0
rollback tran test_calculate_mix
commit tran test_calculate_mix
Server: Msg 8501, Level 16, State 1, Line 3
MSDTC on server 'SQLServerA' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d01c].
what could possibly be wrong here?
I have tried putting SET XACT_ABORT ON but still it doesn't work.
Has anyone experienced this before?