Another solution to trigger synchronization link Server

 

Recently, you need to trigger and synchronize local SQL data to a remote server. First, you need to connect to the server and debug N for a long time. The following error is reported:

Sqloledb cannot use distributed transactions

Unable to perform this operation because the linked server "XXXXX"
OLE DB
Access interface "sqlncli"
You cannot start distributed transactions.

Baidu and Google have not been able to solve this problem for a long time.

Finally, I thought of using xp_mongoshell to execute SQL code without involving distributed transactions. The test passed.

Of course, enabling xp_mongoshell has certain security risks. If it is a public network server, you must do a good job of security first.

 

The Code is as follows:

 

Alter trigger tgr_test on Test2 for insert, update as begindeclare @ cmd varchar (300), @ SQL varchar (200) set @ SQL = 'Update Test Set Name = ''aabb ''' set @ cmd = 'osql-s 202. XX. XX. XX-U sa-P password-D database name-Q "'+ @ SQL +'" 'exec master. DBO. xp_mongoshell @ cmdend

 

 

 

 

 

 

Attached

Unable to perform this operation because the linked server "XXXXX"
OLE DB
Access interface "sqlncli"
You cannot start distributed transactions.

Online Solution

Ii. solution 1.
Both parties start the MSDTC Service

MSDTC provides the distributed transaction service. To use distributed transactions in the database, you must start MSDTC (distributed
Transaction Coordinator) service.

2.
Open port 135 of both parties

MSDTC depends on the Remote Procedure Call (RPC) service. RPC uses port 135 to ensure that the RPC service is started. If the server has a firewall, port 135 is not blocked by the firewall.

Use "Telnet IP 135
"Command to test whether the other port is open to the outside world. You can also use port scanning software (such as advanced port scanning) to scan the port to determine whether the port is open.

3.
Ensure that the statements on the linked server do not access the operations on the initiating Transaction Server

The server that initiates the transaction executes the query, view, or stored procedure on the linked server to access the operations on the initiating Transaction Server. Such operations are called loopback and are not supported, therefore, make sure that this operation does not exist on the linked server.

4.
Add the set xact_abort on statement before the transaction starts.

For most Ole Databases
The provider (including SQL Server) must implicitly or display the xact_abort in the transaction data modification statement
Set to on. The only case where this option is not required is when the Provider supports nested transactions.

5.
MSDTC settings

Open "management tools-component services", enable "component services-computer", and right-click "my computer. On the MSDTC tab, click "Security Configuration.

Make the following settings in the Security Configuration window:

L
Select "Network DTC Access"

L
In Client Management, select "allow remote client" and "Allow Remote Management"

L
Select "Allow inbound" "Allow outbound" in transaction management communications, and do not require verification"

L
Ensure that the DTC login account is: nt authority \ NetworkService

6.
Link server and name resolution Problems

There are two common scenarios for creating a link to an SQL Server:

L
In the first case, select "SQL Server" for the product"

Exec sp_addmediaserver

@ Server = 'linkservername ',

@ Srvproduct = n' SQL Server'

In this case, @ Server
(Linkservername) is the name or IP address of the sqlserver server to be linked.

L
In the second case, select "Microsoft ole db Provider SQL Server" or "SQL native client" as the access interface"

Exec sp_addmediaserver

@ Server = 'linkservername ',

@ Srvproduct = '',

@ Provider = 'sqlncl ',

@ Datasrc = 'sqlservername'

In this case, @ datasrc (sqlservername) is the actual sqlserver server name or IP address to be linked.

 

The SQL Server database engine accesses the linked server through the server name or IP address set above. The DTC service only accesses the linked server through the server name address, therefore, make sure that both the database engine and DTC can access the linked server through the server name or IP address.

The methods used by the database engine and the DTC parsing server are not the same.

6.1
Database Engine

In the first case, @ server, or in the second case, when @ datasrc is set to an IP address, the database engine accesses the linked server based on the IP address, and no name resolution is required.

In the first case, @ server or in the second case, @ datasrc is set to SQL.
To resolve a server name, you must resolve the server name to an IP address.

There are two ways to resolve the server name:

First, set an alias in the SQL Server Client configuration to correspond the above server name to the IP address of the linked server.

Second, add a record to the "C: \ windows \ system32 \ drivers \ etc \ hosts" file:

XXX. XXX
Server Name

It also maps the server name to the IP address of the linked server.

6.2
DTC

In either case, as long as @ server is set to the server name rather than the IP address, name resolution is required. The method is the same as the method above. Add resolution records in the hosts file, the first method above does not work for DTC.

If @ server is configured with an IP address, domain name resolution is not required.

 

7.
Name resolution on Remote Server

The servers that participate in distributed transactions need to access each other. The server that initiates the query must search for the remote server based on the machine name or IP address, and the remote server must also search for the initiator server, the remote server searches for the server by the machine name of the initiating server. Therefore, ensure that the remote server can access the initiating server by the machine name of the initiating server.

Generally, the machine names of two servers in the same network segment can be well resolved, but it is not guaranteed that they can be well resolved. Therefore, it is safer:

Add a record to the remote server in the "C: \ windows \ system32 \ drivers \ etc \ hosts" file:

XXX. XXX
Initiator server name

 

 

Sp_configure 'show advanced options', 1reconfiguregosp_configure 'xp _ Your shell', 1 reconfigurego