服务器“服务器不可用"上的MSDTC [英] MSDTC on server 'server is unavailable'
问题描述
我在SQL Server上收到此奇怪的错误.而且我在较旧的帖子中找不到解决方案.
I get this weird error on SQL Server. And I cannot find solution in older posts.
我有此过程:
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
当我在Management Studio中运行程序时,如下所示:
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
我收到此错误:
消息8501,级别16,状态3,过程_upJM_SyncAll_test,第54行
服务器服务器"上的MSDTC不可用.
Msg 8501, Level 16, State 3, Procedure _upJM_SyncAll_test, Line 54
MSDTC on server 'server' is unavailable.
过程_upJM_SyncAll_test只有39行...
Procedure _upJM_SyncAll_test has only 39 lines...
推荐答案
对于我来说,该服务已已停止.解决方案:需要打开MSDTC服务
In my case, the service was stopped. solution: need to turn the MSDTC service on
- 转到服务. (开始>设置>控制面板>管理工具>服务)
- 找到名为"分布式事务处理协调器"的服务,然后 右键单击(在其上并选择)> 开始.
- 使此服务自动运行 ,以永久解决此问题
- go to Services. (START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES)
- Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.
- make this service to run Automatically for solving this issue permanently
这篇关于服务器“服务器不可用"上的MSDTC的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!