8

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:

enter image description here

1 Answer 1

11

When you do INSERT INTO...EXEC locally, sql server will honor it, but when you use Linked server, sql server has to initiate a local transaction and promote / esclate to a distrubuted transaction.

From --> How to create an autonomous transaction in SQL Server 2008

'remote proc transaction promotion' is a new option on SQL Server 2008, which allows you to control whether or not you want to enlist remote stored procedure call in a distributed transaction. When this option is off (FALSE), the local transaction will not be promoted to distributed transaction. This is how we are able to separate outer and inner transactions in a "autonomous transaction" fashion.

As far as I am aware, there is no security implication.

Also, refer to sp_serveroption for more details.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.