- When data is less, you can manually synchronize, more convenient to control
- Instances of two tables instant synchronization with triggers:
- 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 - Table 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 in this unit - the work on the machine is built, and the working IF exists for synchronization processing is (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) PRIMARY Key?, Name Varchar (10)) GO
- Creating synchronized trigger CREATE TRIGGER T_TEST ON TESTFOR INSERT, UPDATE, DELETEASSET? XACT_ABORT ON - MSDTC Services of Start Remote Server EXEC MSDTC Services EXEC MASTER.. cP_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 name identity 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 Note: Collected from Zou Jian Forum answered questions
