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...
Triggers are included in the implicit transaction required for insert, update, and delete statements. Because you are connecting to a linked server within a transaction, SQL Server promotes it to a Distributed Transaction.
You'll need to configure MSDTC, you can either open MMC and load the MSDTC plugin or use the following script to open inbound and outbound transactions.
https://technet.microsoft.com/en-us/library/cc731495.aspx
I ran into the same error, however it wasn't as simple as the Distributed Transaction Coordinator service from not running. I received a driver update automatically through windows that was causing issues with COM+ and not allowing MSDTC to communicate properly even though the MSDTC service was running. In my case, it was an issue with HP hotkey drivers but in researching I found other reports of issues with audio drivers from other manufacturers causing this as well.
To check to see if you have a similar issue, launch Component Services (dcomcnfg.exe), then expand Component Services > Computers > My Computer, from here click on 'COM+ Applications' to see if an error will pop-up with "COM+ unable to talk to Microsoft Distributed Transaction Coordinator" or there will be a red error over the icon for My Computer in the navigation.
The fix for me was to disable the 'HP Hotkey Service" and "HotKeyServiceUWP" services. Once those were disable, MSDTC immediately started working.
'Distributed Transaction Coordinator' service was not running, So started service and changed service type to automatic too.
In my case, the service was stopped. solution: need to turn the MSDTC service on