Can someone help me to troubleshoot this error?
Error:OLE DB provider "SQLNCLI11" for linked server "TESTSERV"
returned message "No transaction is active.". Msg 7391, Level 16,
State 2, Line 7 The operation could not be performed because OLE DB
provider "SQLNCLI11" for linked server "TESTSERV" was unable to begin
a distributed transaction.
I am getting the error executing the code below.
CREATE TABLE #Drive
(
drive char(1) Primary Key,
freespace int null
)
Insert into #Drive
EXEC TESTSERV.MASTER.xp_fixedDrives
I've already done the following set up:
Allow Remote Clients
Allow Outbound
On Security tab of Local DTC Properties in Component Services:
- Go To Run, type comexp.msc.
- Double click "Console Root".
- Double click "Component Services.
- Double click "Computers".
- Double click "My Computer".
- Double click "Distributed Transaction Coordinator".
- Right click "Local DTC" under "Distributed Transaction Coordinator", and click properties.
- Click the "Security" tab.
- Put tick marks on the checkboxes "Allow Remote Clients" and "Allow Outbound".
Still not fixed.
Different clone is used here for remote machine.
Best Answer
Map a specific instance of SQL Server to a specific instance of MSDTC?
To map a SQL Server instance to an instance of MSDTC, open an administrative command prompt and use the
msdtc.exe
command.The following command is used to create a mapping between an instance of SQL Server and an instance of MSDTC.
https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/15/msdtc-recommendations-on-sql-failover-cluster/