Linked Server Error–remote proc transaction promotion

Problem:  

I have two SQL Servers Server A and Server B. Both have SQL Server 2008. I needed to fetch data from server B to server A and insert it into a table on server A.  For this purpose, I had created a stored proc on server B and for calling that SP I have added server B as linked server on server A.  When I executed this SP from server A using server B, it returns data without any issue.

However when I try to insert this data to a local table on server A, I am getting following error:

OLE DB provider “SQLNCLI10” for linked server “LinkedServerName” returned message “The transaction manager has disabled its support for remote/network transactions.”.
Msg 7391, Level 16, State 2, Server <ServerName, Procedure <SP Name>, Line 47
The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “LinkedServerName” was unable to begin a distributed transaction.

 

Solution:

Resolved the issue by changing the linked server property with below command.

EXEC sp_serveroption @server = ‘ServerB’,@optname = ‘remote proc transaction promotion’, @optvalue = ‘false’ ;

 

 

 

Advertisements