Today I worked with a developer who was trying to query some
data from a remote instance and inserting into a local instance, he told me
that he created the linked server locally for the abc-sqlops01 instance and
made sure that all fine. And also he specified that the test connection of the
linked server was succeeded and simple SELECT statement is working fine but
when he tried to run a stored procedure he is getting the errors as bellow.
Executed statement:
INSERT INTO datalist
EXEC [abc-sqlops01].Updatelist.dbo.[GetUpdatelist] 'opstestdata'
Error:
OLE DB provider "SQLNCLI"
for linked server "abc-sqlops01" returned message "The partner
transaction manager has disabled its support for remote/network
transactions.".
Msg 7391, Level 16, State 2, Line 8
The operation could not be
performed because OLE DB provider "SQLNCLI" for linked server "abc-sqlops01"
was unable to begin a distributed transaction.
Work Around:
Start àAdministrative
Tools àComponent
Services
This will open the Component Services console
Expand the Component Services àDistributed
Transaction Coordinator
Right click
on the Local DTC àProperties
On the
Local DTC Properties window, click on the Security Tab
Check
Network DTC Access under Security Settings
In
the Transaction Manager Communication box, Check Allow Inbound and Allow
Outbound (or check either one based on
your business requirement) and check the
required Authentication method as well as required. In my case I checked “No
Authentication Required”.
Refer below screen shot for reference.