MSDTC on server 'server is unavailable'

Question

I get this weird error on SQL Server. And I cannot find solution in older posts.

I have this procedure:

create proc _upJM_SyncAll_test as begin     DECLARE @SQLString nvarchar(max)  set @SQLString = N' DELETE FROM OPENQUERY([LOCAL_MYSQL],''SELECT acSubject FROM _utjm_setitemprices'') where acSubject not in (select acSubject from _uvJM_SetSubj) DELETE FROM OPENQUERY([LOCAL_MYSQL],''SELECT acSubject FROM _utjm_setsubj'') where acSubject not in (select acSubject from _uvJM_SetSubj)  update a set acName2 = b.acName2,     acName3 = b.acName3,     acAddress = b.acAddress,     acPost = b.acPost,     acPostName = b.acPostName,      acCountry = b.acCountry,      acVATCodePrefix = b.acVATCodePrefix,     acCode = b.acCode,      anDaysForPayment = b.anDaysForPayment from OPENQUERY([LOCAL_MYSQL],''SELECT * FROM _utjm_setsubj'') a join _uvJM_SetSubj b on (a.acSubject = b.acSubject) where 1=1 and (   isnull(a.acName2,'''') <> isnull(b.acName2,'''') OR          isnull(a.acName3,'''') <> isnull(b.acName3,'''') OR          isnull(a.acAddress,'''') <> isnull(b.acAddress,'''') OR          isnull(a.acPost,'''') <> isnull(b.acPost,'''') OR          isnull(a.acPostName,'''') <> isnull(b.acPostName,'''') OR          isnull(a.acCountry,'''') <> isnull(b.acCountry,'''') OR          isnull(a.acVATCodePrefix,'''') <> isnull(b.acVATCodePrefix,'''') OR          isnull(a.acCode,'''') <> isnull(b.acCode,'''') OR          isnull(a.anDaysForPayment,'''') <> isnull(b.anDaysForPayment,'''') )  insert into OPENQUERY([LOCAL_MYSQL],''SELECT * FROM _utjm_setsubj'') (acSubject, acName2, acName3, acAddress, acPost, acPostName, acCountry, acVATCodePrefix, acCode, anDaysForPayment) select b.acSubject, b.acName2, b.acName3, b.acAddress, b.acPost, b.acPostName, b.acCountry, b.acVATCodePrefix, b.acCode, b.anDaysForPayment from OPENQUERY([LOCAL_MYSQL],''SELECT * FROM _utjm_setsubj'') a right join _uvJM_SetSubj b on (a.acSubject = b.acSubject) where a.acSubject is null '  EXECUTE sp_executesql @SQLString; end 

When I run procedure in management studio like this:

  exec dbo._upJM_SyncAll_test 

everything is OK. I get no error, sync is working just fine.

But when I put execute in trigger like this:

create trigger _utrJM_SetSubj on tHE_SetSubj after insert, update, delete as begin     exec dbo._upJM_SyncAll_test end 

I get this error:

Msg 8501, Level 16, State 3, Procedure _upJM_SyncAll_test, Line 54
MSDTC on server 'server' is unavailable.

Procedure _upJM_SyncAll_test has only 39 lines...

Answer 1

In my case, the service was stopped. solution: need to turn the MSDTC service on

1. go to Services. (START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES)
2. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.
3. make this service to run Automatically for solving this issue permanently