I've a a linked server and I need to run the following statement:
INSERT INTO...EXEC linkedserver.sp @parameter
Both servers SQL Server 2008R2 SP1. Once I run it I get this error:
Msg 7391, Level 16, State 2, Line 6 The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "MY.LINKED.SERVER" was unable to begin a distributed transaction.
After searching for the error I saw that lot recommend running:
EXEC master.dbo.sp_serveroption @server = N'[mylinkedserver]', @optname = N'remote proc transaction promotion', @optvalue = N'false'
Is there any security implications on this action I should be aware of?
Other option is to use advanced properties of the linked server object on main server: