Use the Link SERVER of SQL Server to implement distributed transactions

The options ansi_warning and ansi_nulls must be enabled, as if they do not affect the system.

 

The landlord checks two places again. After testing, this must be set when you want to use distributed transactions.

1. Link server and name resolution Problems

-- Create a linked server

EXEC sp_addmediaserver 'srv _ lnk ', 'ms', 'sqlodb', '192. 168.1.1'

GO

-- Create a ing between remote logins on the linked server

EXEC sp_add1_srvlogin 'srv _ lnk ', 'false', NULL, 'sa', '123'

GO

Add a record to the "C: \ WINDOWS \ system32 \ drivers \ etc \ hosts" file:

Xxx. xxx server name

Xxx. xxx launch server name

2. Firewall settings

Both firewalls add exception programs c: \ windows \ system32 \ msdtc.exe

 

Windows Firewall is enabled on the computer. By default, Windows Firewall blocks the MSDTC program.

Please note that this problem may occur even if Windows Firewall is disabled.

Bytes ------------------------------------------------------------------------------------

SQL Server 2005 distributed transaction environment setup

 

1. Run the batch processing program and install and start the msdtc Service.

@ Echo off

Setlocal

@ Echo % WINDIR % \ System32 \ msdtc.exe-uninstall

% WINDIR % \ System32 \ msdtc.exe-uninstall

Call: delkey "HKCR \ CID"

Call: delkey "HKLM \ SYSTEM \ CurrentControlSet \ Services \ MSDTC"

Call: delkey "HKLM \ SYSTEM \ ControlSet001 \ Services \ MSDTC"

Call: delkey "HKLM \ SYSTEM \ ControlSet002 \ Services \ MSDTC"

Call: delkey "HKLM \ Software \ Microsoft \ MSDTC"

@ Echo % WINDIR % \ System32 \ msdtc.exe-install

% WINDIR % \ System32 \ msdtc.exe-install

Endlocal

Goto: EOF

: Delkey

Set key = % 1

Call: delkeyq % key %> nul 2> & 1

@ Echo.

Goto: EOF

: Delkeyq

REG Delete % key %/F

2. Open Port 35 (Remote Procedure Call) in both firewalls)

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. Run the "telnet IP 135" command to test whether the peer 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.

Enable the rpc out option on both servers.

 

Code: SQL

1	exec sp_serveroption 'test2005', 'rpc out', 'true

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 db providers (including SQL Server), XACT_ABORT In the implicit or display transaction data modification statement must be 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:

Select "Network DTC Access"

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

Select "Allow inbound", "Allow outbound", and "do not require verification" in the transaction management communication"

Ensure that the DTC login account is: NT Authority \ NetworkService

Bytes ----------------------------------------------------------------------------------------------------

-- Create a link on the initiator server xiaodi (Windows XP) to the server EXEC sp_add+server 'cpserver', 'ms', 'sqloledb', and '75. 81.1.220 'goexec sp_addjavassrvlogin 'cpserver', 'false', NULL, 'sa ', '000000' GO -- On the second Server cpserver (Windows 123 Server) the connection server EXEC sp_addrole server 'xiaodi ', 'ms', 'sqloledb', '75. 81.2.37 'goexec sp_addjavassrvlogin 'xiaodi ', 'false', NULL, 'sa', '000000' GO -- connect to the initiator server xiaodi 75.81.2.37, create a stored procedure use ksoa_demoGOif exists (select top 1 1 from sysobjects where xtype = 'p' and name = 'dtc _ test ') drop procedure dct_testGOcreate procedure dtc_testas set xact_abort ON begin distributed tran dtc_test update bmdoc set bm = 'department of Management 1' where bmid = 'dbm00000001 'exec [test] 2005. ksoa_test_quyu.dbo.dtc_test commit tran dtc_test set xact_abort OFFgo -- connect to the second server cpserver 75.81.1.220, create a stored procedure use ksoa_test_quyuGOif exists (select top 1 1 from sysobjects where xtype = 'p' and name = 'dtc _ test ') drop procedure dtc_testGO create procedure dtc_testas tran in tran dtc_test1 update bmdoc set bm = 'hr 1' where bmid = 'dbm00000053 'commit tran dtc_test1go -- connect to the initiator server xiaodi, execute the Stored Procedure exec dtc_test -- view the result select * from [test2005]. ksoa_test_quyu.dbo.bmdocselect * from bmdoc -- Note: if the statement execution on the cpserver of the second server fails, initiate the service