4

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ronald Quesada Madriz
  • 41
  • 1
  • 2

1 Answers1

5

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.

swasheck
  • 4,644
  • 2
  • 29
  • 56