-Regularly synchronize data on the server (data modifications on the server are synchronized to the local device)

-- Regularly synchronize data on the server (data changes on the server are synchronized to the local device) -- test environment, SQL Server2000, remote server name: rserver, User name: ruser, password: rpwd, test Database: Test -- the table on the server (the query analyzer is connected to the server to create) -- The state field is an additional field set for secondary updates. Field Value Description: NULL indicates a new record, 1 indicates the modified record, and 0 indicates the record without change if exists (select * From DBO. sysobjects where id = object_id (n' [luser] ') and objectproperty (ID, n'isusertable') = 1) drop table [luser] Go create table [luser] (ID int identity (1,1) primary key, [user] varchar (4), PWD varchar (10 ), address varchar (50), State bit) Go -- create a trigger, maintain the value of the state field create trigger t_state on [luser] After update as update [luser] Set state = 1 from [luser] A join inserted B on. id = B. ID where. state is not null go ---- if exists (select * From DBO. sysobjects where id = object_id (n' [luser] ') and objectproperty (ID, n'isusertable') = 1) drop table [luser] Create Table [luser] (ID int primary key, [user] varchar (4), PWD varchar (10), address varchar (50 )) go -- to facilitate synchronization, create a connection server to the server to be synchronized -- remote server name: rserver, User name: ruser, password: rpwd if exists (select 1 from master .. sysservers where srvname = 'srv _ lnk ') exec sp_dropserver 'srv _ lnk', 'droplogins' go exec sp_add1_server 'srv _ lnk ', '', 'sqloledb ', 'rserver' exec sp_add1_srvlogin 'srv _ lnk ', 'false', null, 'ruser', 'rpwd' go -- create a stored procedure for synchronous processing if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_synchro] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [p_synchro] Go create proc p_synchro as/* -- because the MSDTC Service (required for Distributed Transaction Processing) is unstable, so the following part is the selection content -- set xact_abort on -- start the MSDTC Service of the remote server -- exec master .. xp_mongoshell 'isql/s "rserver"/u "ruser"/P "rpwd"/Q "Exec master .. xp_mongoshell ''net start MSDTC '', no_output" ', no_output -- start the local MSDTC Service -- 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 -- */-- to synchronize the deleted data Delete from [luser] Where id not in (select ID from srv_lnk.test.dbo. [luser]) -- synchronize the newly added data insert into [luser] Select ID, [user], PWD, address from srv_lnk.test.dbo. [luser] Where state is null -- synchronize the modified data update [luser] Set [user] = B. [user], Pwd = B. PWD, address = B. address from [luser] A join srv_lnk.test.dbo. [user] B on. id = B. ID where B. state = 1 -- Update srv_lnk.test.dbo on the server after synchronization. [luser] Set state = 0 where isnull (State, 1) = 1 -- commit tran -- if distributed transaction processing is enabled, add this sentence go -- create a job, if exists (select 1 from MSDB .. sysjobs where name = 'data') execute MSDB. DBO. sp_delete_job @ job_name = 'data' exec MSDB .. sp_add_job @ job_name = 'data' -- create job step declare @ SQL varchar (800), @ dbname varchar (250) Select @ SQL = 'exec p_synchro '-- Data Processing Command, @ dbname = db_name () -- Name of the database that executes data processing exec MSDB .. sp_add_jobstep @ job_name = 'data', @ step_name = 'data synchronization', @ subsystem = 'tsql', @ database_name = @ dbname, @ command = @ SQL, @ retry_attempts = 5, -- retry times @ retry_interval = 5 -- Retry Interval -- create a scheduled exec MSDB .. sp_add_jobschedule @ job_name = 'data', @ name = 'schedule ', @ freq_type = 4, -- 4 daily, 8 weeks, 16 months @ freq_interval = 1, -- job execution days @ freq_subday_type = 0, -- whether to execute the job repeatedly. 0x1 is at the specified time, 0x4 minutes, 0x8 hours @ freq_subday_interval = 1, -- repeat cycle @ freq_recurrence_factor = 0, -- repeat execution, set to 1; otherwise, set to 0 @ active_start_time = 00000 -- start execution at -- add the target server exec MSDB. DBO. sp_add_jobserver @ job_name = @ jobname, @ SERVER_NAME = n' (local) 'Go