SQL Server synchronizes data across databases, SQL Server

SQL Server synchronizes data across databases, SQL Server

Recently, cross-database data synchronization is required. The two databases are distributed on two physical computers. Automatic and regular synchronization can be achieved through SQL Server proxy jobs, but the premise is that you need to write a stored procedure to implement synchronization logic processing. The stored procedure here is not implemented by opendatasource, but by using a linked server. The stored procedure is created on the IP1: 192.168.0.3 server. You need to synchronize the customer information of the view v_custom to the t_custom table on the IP2: 192.168.0.10 server. If the logic does not exist, insert it. If yes, update the field.

Create PROCEDURE [dbo]. [p_pm _ project platform customers synchronize data to the reimbursement platform in batches] (@ destserver nvarchar (50), @ sourceserver nvarchar (50) asbegin set nocount on; -- if no data exists, add the linked server, the IP address must be specified in the external query, for example, select * from [IP]. [database]. [dbo]. [table] if not exists (select * from sys. servers where server_id! = 0 and data_source = @ destserver) begin exec sp_addmediaserver @ server = @ destserver end if not exists (select * from sys. servers where server_id! = 0 and data_source = @ sourceserver) begin exec sp_addlinkedserver @ server = @ sourceserver end begin try set xact_abort on begin transaction insert into [192.168.0.10]. [dbCRM]. [dbo]. [t_custom] (customer ID, customer name, customer abbreviation, enter code, query code, address, enter login name, enter time, modify login name, change time, approval status ID, approval Status name, whether approval is complete, approval operation time, project management merchant code, serial number) select. customer ID,. customer name,. customer abbreviation, dbo. fn_pm_GetPy (. customer name),. customer ID + ',' +. customer name + ',' + dbo. fn_pm_GetPy (. customer name) + ',' +. customer abbreviation + ',' + dbo. fn_pm_GetPy (. customer abbreviation),. address, 'admin', getdate (), null, null, 'd65f87a8-79C8-4D1C-812D-AE4591E056A8 ', 'Approved', 1,. approval operation time,. project management merchant code, 0 FROM [dbPM]. [dbo]. [v_custom] a where. customer id not in (SELECT customer id from [192.168.0.10]. [dbCRM]. [dbo]. [t_custom]); ---------------------------------- updates --------------------------------------- update A set. customer name = B. customer name,. customer abbreviation = B. customer abbreviation,. input code = dbo. fn_pm_GetPy (B. customer name),. query code = B. customer ID + ',' + B. customer name + ',' + dbo. fn_pm_GetPy (B. customer name) + ',' + B. customer abbreviation + ',' + dbo. fn_pm_GetPy (B. customer abbreviation),. address = B. address,. modify login name = 'admin',. modification time = getdate (),. project management merchant code = B. project management merchant code from [192.168.0.10]. [dbCRM]. [dbo]. [t_custom] A, [dbPM]. [dbo]. [v_custom] B where. customer ID = B. customer ID; commit transaction end try begin catch select ERROR_NUMBER () as errornumber, ERROR_MESSAGE () as errormsg, ERROR_LINE () as errorline rollback transaction end catchEND

If the message is not correctly configured, message 7391, level 16, status 2, process xxxxx, and line XX are often displayed. This operation cannot be performed because the ole db access interface "SQLNCLI" of the linked server "xxxxx" cannot start distributed transactions.

You can refer to the following Configuration:

The above is how SQL Server synchronizes data across databases. I hope it will be helpful for your learning.

Articles you may be interested in:

• Sqlserver 2000 synchronize the content of two SQLServer Databases
• SQL Server 2000 detailed steps for Database Synchronization [two servers]
• Tutorial on synchronizing data between SQLServer2005 and SQLServer2008 Databases
• SQLServer cross-database query Implementation Method
• Detailed description of SqlServer2005 Database Synchronization Configuration
• SQL Server dual-Server setup and automatic data synchronization tutorial
• Use Sqlserver transaction publishing for data synchronization (sql2008)
• SQL Server 2005 image construction manual (sql2005 database synchronous image solution)
• Synchronous Database Backup through SQL Server 2008 Database Replication
• Two Methods for SQL Server Database Synchronization (publishing and subscription)