Sql server Linked Server Issue

Question

Sql server Linked Server Issue

Vinod Mallolu 1

Hello Experts,
I have a linked server between server1 and server2.

Sql server 2012 with sp4

Linked server is working fine for everything.

But when trying to insert from server1 to server2
With
Begin Tran, it’s not allowing to insert. Is there a limitation?

Tried Begin Distributed transaction as well.

Not working.

Please help.

Thanks in advance.
Vinod

Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2021-11-18T22:56:18.063+00:00

What error message do you get?

Getting distributed transactions to work can be a smooth sailing, but it can also be a very tough battle.

Are your servers in a domain, or are they in a workgroup?
Vinod Mallolu 1 Reputation point

2021-11-19T02:15:53.783+00:00

Hi,
Below is the error I’m getting.

The operation could not be performed because of OLE DB provider SQLNCLI11 for linked server (IP address) was unable to begin a distributed transaction.

Actually, we are getting problem only when we use Begin Tran. Else, it’s absolutely working fine. And both are in the same domain. One from Azure and another from AWS.
YufeiShao-msft 7,151 Reputation points

2021-11-23T08:09:28.65+00:00

Please do not forget to mark as accepted answer wherever information provided to you were helpful.

Regards

2 answers

Your answer

Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2021-11-18T22:56:18.063+00:00

What error message do you get?

Getting distributed transactions to work can be a smooth sailing, but it can also be a very tough battle.

Are your servers in a domain, or are they in a workgroup?
Vinod Mallolu 1 Reputation point

2021-11-19T02:15:53.783+00:00

Hi,
Below is the error I’m getting.

The operation could not be performed because of OLE DB provider SQLNCLI11 for linked server (IP address) was unable to begin a distributed transaction.

Actually, we are getting problem only when we use Begin Tran. Else, it’s absolutely working fine. And both are in the same domain. One from Azure and another from AWS.
YufeiShao-msft 7,151 Reputation points

2021-11-23T08:09:28.65+00:00

Please do not forget to mark as accepted answer wherever information provided to you were helpful.

Regards

Answer 1

YufeiShao-msft 7,151

Hi @Vinod Mallolu ，

If you can describe your error in detail, we will be able to help you more accurately, so what kind of error alert did it give you?

Please note that your firewall may prevent you from doing so

When you start distributed transactions that involve SQL Server Database Engine as a resource, you might encounter various error messages if the MSDTC configuration is not correct.
https://support.microsoft.com/en-us/topic/recommended-msdtc-settings-for-using-distributed-transactions-in-sql-server-79ec3905-ab14-00b6-d5e3-14e549f53882

Here is an error that may be relevant to you:
https://social.msdn.microsoft.com/Forums/en-US/f66a228d-0ae7-4e6e-a4ee-30a03279168b/how-to-insert-a-record-into-table-from-one-server-to-another-server-in-trigger-

Vinod Mallolu 1 Reputation point

2021-11-19T05:35:25.98+00:00

Hi,
The error is below. I changed IP address

OLE DB provider SQLNCLI11 for linked server ()
Returned message No transaction is active.

Msg 7391, Level 16, State 2, Line 5

The operation could not be performed because OLEDB provider SQLNCLI11 for linked server () was unable to begin distributed transaction.

As I said, everything is working well if we don’t use Begin Tran

Thanks
Vinod
YufeiShao-msft 7,151 Reputation points

2021-11-19T06:20:34.293+00:00
Hi, This is most likely still about MSDTC, to confirm this, you can look for possible error messages in the Event Log.

running:

msdtc -uninstall msdtc -install

and then Rebooted the server
https://www.sqldbadiaries.com/2014/09/24/no-transaction-is-active-message-when-accessing-linked-server/

You can also try to solve the problem by disabling "Enable Promotion of Distributed Transactions for RPC":

EXEC master.dbo.sp_serveroption @server = N'[mylinkedserver]', @optname = N'remote proc transaction promotion', @optvalue = N'false'

https://stackoverflow.com/questions/7473508/unable-to-begin-a-distributed-transaction
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2021-11-19T22:37:14.777+00:00

You can also try to solve the problem by disabling "Enable Promotion of Distributed Transactions for RPC":

I guess that Vinod brackets the INSERT with BEGIN TRANSACTION, this is because he wants a transaction, so that the INSERT is rolled back if there is an error later. So disabling that option does not seem to be a useful solution.

Answer 2

Olaf Helper 47,586

You have to setup MS DTC = Distributed Transaction Coordinator

Vinod Mallolu 1 Reputation point

2021-11-23T11:51:39.74+00:00

How can I do this like MSDTC = distributed transaction coordinator?
Erland Sommarskog 129.1K Reputation points MVP Volunteer Moderator

2021-11-23T21:58:38.31+00:00

MSDTC must be running on both machines. Which it usually is.

I will have to admit that MSDTC is one of these things I have never grasped very well.

My experience is it that it usually works when the machines are in the same domain. If the machines are in different domains without trust or in workgroups, it is a very tough battle.

Now, you said that the machines are in the same domain - but for some extra spice they are in different clouds. My head spins when I hear this, but this way outside my expertise.

Share via

Sql server Linked Server Issue

2 answers

Your answer