SQL Server 2008:具有触发器的表上的MERGE命令导致错误 [英] SQL Server 2008: MERGE command on a table that has a trigger causes an error

问题描述

我有一个存储过程，该存储过程使用MERGE命令将dbo.tableA与[mylinkedserver] .dbo.TableA同步.

TableA具有与其关联的插入/更新触发器.为了使事情变得非常简单，所有触发器要做的就是

I have a stored procedure that uses the MERGE command to synchronize dbo.tableA with [mylinkedserver].dbo.TableA.

TableA has an insert/update trigger associated with it. To keep things really simple all the trigger does is

print ''I am a simple trigger because i dont want to cause errors''

当执行SP(通过.net Windows应用程序)时，它将引发此错误:

When the SP is executed (thru a .net windows application), it throws this error:

OLE DB provider "SQLNCLI10" for linked server "MyLinkedServer" returned message "No transaction is active."

如果我删除触发器并再次执行存储的proc，它将执行得很好.

如果我通过SSMS运行存储过程，则存储过程也可以正常运行(使用触发器).

两台服务器上均启用了MSDTC.

服务器是Windows 2008服务器，带Service Pack 2的SQL Server2008.

为什么触发器会导致此错误?！?！??

If I delete the trigger and execute the stored proc again, it executes absolutely fine.

The stored procedure also runs fine (with the trigger) if i run it through SSMS.

MSDTC is enabled on both servers.

Server is Windows 2008 server, SQL Server 2008 with service pack 2.

Why would a trigger cause this error ?!?!??

推荐答案

修复链接

链接服务器"的OLE DB提供程序"SQLNCLI10"返回了消息没有事务处于活动状态". . [ ^ ]

Fix link

OLE DB provider "SQLNCLI10" for linked server "" returned message "No transaction is active.".[^]

这篇关于SQL Server 2008:具有触发器的表上的MERGE命令导致错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！