0

Can someone help me to troubleshoot the below error?

I am getting the error executing the below code.

CREATE TABLE #Drive
(
    drive char(1) Primary Key,
    freespace int null
)

Insert into #Drive
EXEC TESTSERV.MASTER.xp_fixedDrives

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 already done below 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 get fixed. Different clone is used here for remote machine.

  • After you made the DTC changes, did you restart the DTC service? You may also find some helpful information about How to troubleshoot connectivity issues in MS DTC by using the DTCPing tool – Scott Hodgin Oct 23 '17 at 13:16
  • Also - the DTC service is running on both servers, and is properly set up on both - correct? – RDFozz Oct 23 '17 at 21:32
  • Thanks for looking into the issue. Yes i restarted the services after made the DTC changes in both the server but no luck. – Rashmita Shanti Oct 24 '17 at 10:36
  • Have you looked at linked server properties, to see if remote calls, EnablePromotionTransaction Coordinator enabled. – Dina Apr 20 '18 at 23:52
0

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.

msdtc -tmMappingSet -name <MappingName> -service <SQLServerServiceName> ^
-ClusterResourceName <MSDTCResourceName>

https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/15/msdtc-recommendations-on-sql-failover-cluster/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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