SQL Server synchronizes data across libraries

Original: SQL Server synchronizes data across libraries

A recent requirement is to synchronize data across libraries, where two databases are distributed over two physical computers, and automatic periodic synchronization can be done through SQL Server Agent jobs, but only if a stored procedure is written to implement synchronous logic processing. The stored procedure here is not opendatasource, but is implemented using a linked server. The stored procedure is created on the ip1:192.168.0.3 server, and you need to synchronize the customer information for the view V_custom 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.

1 Create PROCEDURE [dbo].[P_pm_ Project Platform Customer batch synchronization to reimbursement platform](2     @destserver nvarchar( -),3     @sourceserver nvarchar( -)4 )5  as6 BEGIN7 8     SETNOCOUNT on;9     --add a linked server if it does not exist, an external query must indicate an IP address, such as SELECT * from [IP]. [Database]. [dbo]. [Table]Ten     if  not exists(Select *  fromSys.serverswhereserver_id!=0   andData_source=@destserver) One     begin A         execsp_addlinkedserver@server=@destserver -     End -     if  not exists(Select *  fromSys.serverswhereserver_id!=0   andData_source=@sourceserver) the     begin -         execsp_addlinkedserver@server=@sourceserver -     End -         beginTry +         SetXact_abort on -         begin Transaction  +             --http://www.cnblogs.com/chnking/archive/2007/04/04/699891.html A                 INSERT  into [192.168.0.10].[Dbcrm].[dbo].[T_custom](Customer ID, at                                  - Customer Name, - customer abbreviation, - input Code, - Query Code, - address, in Enter the login name, - entry Time, to Modify the login name, + modification Time, - Approval Status ID, the Approval Status name, * is the approval complete, $ approval operation time,Panax Notoginseng Project management Merchant Code, - serial number) the                 SELECTA. Customer id,a. Customer Name, + A. Customer abbreviation, A dbo.fn_pm_getpy (A. Customer name), theA. Customer number+','+A. Customer Name+','+Dbo.fn_pm_getpy (A. Customer name)+','+A. Customer abbreviation+','+dbo.fn_pm_getpy (A. Client abbreviation), + A. Address, -                                 'Admin', $                                 getdate(), $                                 NULL, -                                 NULL, -                                 'd65f87a8-79c8-4d1c-812d-ae4591e056a8', the                                 'has been approved', -                                 1,Wuyi A. Approval operation time, the A. Project management merchant Code, -                                 0 Wu                  from   [dbpm].[dbo].[V_custom]A -                 WHEREA. Customer ID not inch(SELECTCustomer ID from   [192.168.0.10].[Dbcrm].[dbo].[T_custom]); About              $                                                      -                                                                                      -                 ----------------------------------There are updates-----------------------------------  -                 UpdateASet   AA. Customer Name=B. Customer Name, +A. Customer abbreviation=B. Customer abbreviation, theA. 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, theA. Modifying the login name='Admin', theA. Modification time=getdate(), theA. Project Management Merchant Code=B. Project Management Merchant Code the                      from  [192.168.0.10].[Dbcrm].[dbo].[T_custom]A[dbpm].[dbo].[V_custom]B -                     whereA. Customer ID=B. Customer ID; in              the              the              About             Commit Transaction  the         EndTry the         beginCatch the             SelectError_number () asErrornumber,error_message () asErrormsg,error_line () asErrorLine +             rollback Transaction  -         EndCatch the END

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

You can refer to the following configuration:

See also: http://www.cnblogs.com/chnking/archive/2007/04/04/699891.html

SQL Server synchronizes data across libraries