- Instances of two tables for instant synchronization:
- Test Environment: SQL2000, Remote Host Name: XZ, User Name: SA, Password: No, Database Name: Test
- Create a test table, you can't use the identity column to do the primary key, because you cannot perform normal update - create a test table on this machine, you have to do the same metrics on the remote host, just do not write trigger if exists (SELECT * From dbo.sysObjects where id = Object_id (n '[test]') And ObjectProperty (ID, n'susertable ') = 1) DROP TABLE [TEST]
Create Table Test (ID INT Not Null Constraint PK_Test Primary Key, Name Varchar (10)) Go
- Creating synchronous trigger Create Trigger t_test on testfor insert, update, deleteasset XACT_ABORT ON - Launch remote server MSDTC service exec master..xp_cmdshell 'ISQL / S "xz" / u "sa" / p "" / q "EXEC MASTER..XP_CMDSHELL '' NET Start MSDTC '', NO_OUTPUT", NO_OUTPUT
- Start this machine's MSDTC service exec master..xp_cmdshell 'net start msdtc', no_output
- Distributed transaction processing, if the meigo list is the primary key, use the following method Begin Distributed TransactionDelete 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 Insertedcommit TRANGO
- Insert Data Test INSERT INTO TestSelect 1, 'Aa'Union All Select 2,' B'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 '_123 'Where Id in (3, 5)
- Show test results Select * from test a full linkenrowset ('sqloledb', 'xz'; 'sa'; ', test.dbo.test) b on A.ID = B.ID
