SQL Server _mssql Synchronizing data

A recent requirement is to synchronize data across libraries, two databases are distributed on two physical computers, and automatic periodic synchronization can be done through SQL Server Agent jobs, but only if you need to write a stored procedure to implement synchronization logic. The stored procedures here are not opendatasource, but are implemented using a linked server. The stored procedure is created on the ip1:192.168.0.3 server, and the customer information v_custom The view needs to be synchronized to the T_custom table on the ip2:192.168.0.10 server. The logic is inserted if it does not exist, and the field is updated if it exists.

Create PROCEDURE [dbo]. [P_pm_ Project platform Customer batch synchronization to reimbursement platform]
(@destserver nvarchar, @sourceserver nvarchar (50))
  As BEGIN SET NOCOUNT on; --The addition of a linked server does not exist, and an external query must indicate an IP address, such as SELECT * from [IP]. [Database]. [dbo]. [table] If not EXISTS (SELECT * from Sys.servers where server_id!=0 and data_source= @destserver) begin EXEC Sp_add LinkedServer @server = @destserver End If not EXISTS (SELECT * from Sys.servers where server_id!=0 and data_source= @sou  Rceserver) 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, input code, query code,
                Address, enter login name, input time, modify login name, modify time, approval Status ID,
         Approval status name, whether approval is completed, approval operation time, project management merchant Code,       Serial number) SELECT a. Customer id,a. Customer name, a. Customer abbreviation, dbo.fn_pm_getpy (A. Customer name),
                A. Customer number + ', ' +a. Customer name + ', ' +dbo.fn_pm_getpy (A. Customer name) + ', ' +a. Customer abbreviation + ', ' +dbo.fn_pm_getpy (a. Customer abbreviation), A. Address, ' Admin ', GETDATE (), NULL, NULL, ' d65f87a8-79c8-4d1c-812d
                -ae4591e056a8 ', ' approved ', 1, a. Approval operation time, a. Project management merchant Code, 0 from [dbpm]. [dbo]. [V_custom] A WHERE a. Customer ID not in (SELECT customer ID from [192.168.0.10].[ DBCRM]. [dbo].
      
                          
                                          
        [T_custom]);  ----------------------------------There are updates-----------------------------------update
              A set a. Customer name =b. Customer Name, a. Customer abbreviation =B, a. Input code =dbo.fn_pm_getpy (B. Customer name), A. Query code =B. Customer number + ', ' +b. Customer name + ', ' +dbo.fn_pm_getpy(b. Customer name) + ', ' +b. Customer abbreviation + ', ' +dbo.fn_pm_getpy (b. Customer abbreviation), A. Address =b address, a. Modify login = ' admin ', a. Change Time =getdate (), A. 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 A. Customer id=b. Customer ID; Commit transaction End Try begin catch Select Error_number () as Errornumber,error_message () as Errormsg,e
 Rror_line () as ErrorLine rollback TRANSACTION end Catch end

If not configured correctly, the message will often appear 7391, Level 16, State 2, process xxxxx, XX line. The operation cannot be performed because the OLE DB provider "SQLNCLI" of the linked server "xxxxx" cannot start a distributed transaction.

You can refer to the following configuration:

The above is the SQL Server database synchronization Data implementation method, I hope to help you learn.