sql-server - 服务器上的 MSDTC 'server is unavailable'

Question

我在 SQL Server 上遇到这个奇怪的错误。我在旧帖子中找不到解决方案。

我有这个程序:

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

当我在管理工作室中运行程序时，如下所示:

  exec dbo._upJM_SyncAll_test

一切都好。我没有收到任何错误，同步工作正常。

但是当我像这样将执行放入触发器时:

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

我收到此错误:

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

程序_upJM_SyncAll_test只有39行...

Answer 1

就我而言，服务被停止。解决方案:需要开启MSDTC服务

1. 转到服务。 (开始 > 设置 > 控制面板 > 管理工具 > 服务)
2. 找到名为“分布式事务协调器”的服务并右键单击(并选择)> 开始。
3. 让此服务自动运行以永久解决此问题