SQL Server synchronizes data across libraries

Source: Internet
Author: User
Tags getdate

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.