-- Manual synchronization is supported for less data, making it easier to control
-- Use a trigger to instantly synchronize instances of two tables:
-- Test environment: SQL2000, remote host name: xz, User name: SA, password: None, Database Name: Test
-- Create a test table. The primary key cannot be used as the ID column because normal updates cannot be performed.
-- Create a table on a remote host
If exists (select * From DBO. sysobjects where id = object_id (n' [test] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [test]
Create Table Test (ID int not null constraint pk_test primary key
?, Name varchar (10 ))
Go
-- The following operations are performed on the local machine:
-- Create tables and perform synchronization on the local machine
If exists (select * From DBO. sysobjects where id = object_id (n' [test] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [test]
Create Table Test (ID int identity (1, 1) primary key
?, Name varchar (10 ))
Go
-- Create a synchronization trigger
Create trigger t_test on Test
For insert, update, delete
As
Set? Xact_abort on
-- Start the MSDTC Service of the remote server
Exec master .. xp_mongoshell 'isql/s "xz"/u "sa"/P ""/Q "Exec master .. xp_cmdshell ''net start MSDTC '', no_output" ', no_output
-- Start the MSDTC Service of the Local Machine
Exec master.. xp_mongoshell 'net start MSDTC ', no_output
-- Performs distributed transaction processing. If the table uses the ID column as the primary key, use the following method:
Begin Distributed Transaction
Delete from OpenRowSet ('sqloledb', 'xz '; 'sa'; '', test. DBO. Test)
? Where ID in (select ID from deleted)
Insert into OpenRowSet ('sqloledb', 'xz '; 'sa'; '', test. DBO. Test)
? Select * From inserted
Commit tran
Go
-- Data insertion Test
Insert into test
Select 1, 'A'
Union all select 2, 'bb'
Union all select 3, 'C'
Union all select 4, 'dd'
Union all select 5, 'AB'
Union all select 6, 'bc'
Union all select 7, 'ddd'
-- Delete data test
Delete from test where ID in (1, 4, 6)
-- Update Data Test
Update Test Set Name = Name + '_ 100' where ID in (123)
-- Display Test Results
Select * from test a full join
OpenRowSet ('sqloledb', 'xz '; 'sa'; '', test. DBO. Test) B on A. ID = B. ID
Remarks: Answers to questions collected from forums created by Alibaba Cloud