CODEWITHSUNDEEP
sql-serversql-server-2008triggerslinked-server
Ronald Quesada Madriz
Ronald Quesada Madriz

Reputation: 41

Trigger between linked servers

I have two SQL Server 2008 machines, SQLSERVER1 and SQLSERVER2 (soo original)

In SQLSERVER2, I do:

[SQLSERVER1].[DATABASE].[DBO].StoreProcedure1

All works well.

But if I put into a trigger, I receive this error

OLE DB provider "SQLNCLI10" for linked server "[SQLSERVER1]" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure StoreProcedure1, Line 57
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "[SQLSERVER1]" was unable to begin a distributed transaction.

Upvotes: 4

Views: 4945

Answers (1)

swasheck
swasheck

Reputation: 4693

You need to ensure that your linked servers allow RPC in and out.

Server Objects-> Linked Servers-> Right-click the server and choose Properties -> Server Options

RPC should be "True" and RPC Out should be "True"

You should also ensure that you've enabled DTC Network Access on the servers so that it use distributed transactions.

Upvotes: 5

Related Questions