0

I have a problem with my Linked Server.

I have a Linked Server set up and when I select or insert it works great.

The problem starts when I run a transaction to overwrite simple data in tables.

begin tran
update [LinkedServer].[Database].[dbo].[Table]
set phone = '111111'
where Mobile_phone = '401000010'
rollback

I get an error:

OLE DB provider "MSOLEDBSQL" for linked server "SERVER" returned message "No transaction is active.
Msg 7391, Level 16, State 2, Line 4
The operation could not be performed because OLE DB provider "MSOLEDBSQL" for linked server "SERVER" was unable to begin a distributed transaction.

I don't know where the error could be?

9
  • Didn't you ask this on Stack Overflow the other day? The image you shared there showed that the feature was switched off; have you enabled it now, and you're still getting the error?
    – Thom A
    Oct 24, 2023 at 8:16
  • Ahh yes, you did, and it was switched off. I assumed that you switch it on, it worked, and that was why you deleted the question, so if you're asking again I assume that means you have switched it on now?
    – Thom A
    Oct 24, 2023 at 8:43
  • What do you mean by turn on? Turning on what?
    – Jan Kůst
    Oct 24, 2023 at 10:42
  • If you are talking about enabling distributed transaction propagation is "TRUE" but still the same error
    – Jan Kůst
    Oct 24, 2023 at 11:29
  • @JanKůst can you add details on your question like sql server version of both instance, are they on the same domain, linked server configuration/security, alias, and on what server are you running the update?
    – Dan Co
    Oct 25, 2023 at 0:09

1 Answer 1

0

I have experienced the same error before when configuring the linked server and using distributed transactions. You need to check the DTC configuration on windows OS level below:

Step:

  1. go to dcomcnfg
  2. expand Component Services
  3. expand Computer
  4. expand My Computer
  5. expand Distributed Transaction Coordinator
  6. right click Local DTC, click properties
  7. go to security tab
  8. copy the config from screenshot above
  9. you need to reboot your server

It is also worth checking the firewall rules to see if server communication is open on both ends.

11
  • I have this configuration but a little differently: I have check allow remote client allow remote administration no auth required Enable XA transaction and Enable transaction SNA LU 6.2
    – Jan Kůst
    Oct 24, 2023 at 11:52
  • do you have the luxury to reboot the server? try the config above then reboot your server.
    – Dan Co
    Oct 24, 2023 at 11:57
  • Unfortunately not, but I will write a request. We'll see if it helps
    – Jan Kůst
    Oct 24, 2023 at 12:07
  • @JanKůst Which computer did you configure MSDTC on?...the remote one or the local one or both?
    – J.D.
    Oct 24, 2023 at 12:37
  • Same configuration on both
    – Jan Kůst
    Oct 24, 2023 at 12:45

Your Answer

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

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