Link server for SQL and Oracle Data Synchronization

In many cases, many large units have several systems, and these systems may come from different vendors. The development technologies they adopt may be very different, and the underlying databases may also be

For different platforms, this leads to different "same" information data for the same enterprise, such as employees, departments, process and other information. These are commonly used basic information for enterprises.

Most systems are required. In terms of interaction between these systems, synchronization may be caused by different database platforms and systems, resulting in insufficient information uniformity.

This document describes a common method for database interaction (synchronization)-creating a connection server between databases. I believe this article will help you.

To synchronize data from a linked server, follow these steps:

1. Create a linked server

2. Write a synchronous SQL statement

3. Create a synchronization mechanism

The above content is explained in detail below:

1. Create a linked server

 There are two ways to create a linked server in SQL Server: One is through the wizard, the other is to directly write SQL statements.

A. the Wizard is used to open SQL management studio. The Server Object> linked server> New linked server opens the following dialog box:

Enter a name in the linked server. You can use this name as needed. Select Oracle provider for ole db in the access interface. It is important to write the data source. other options are optional. in this way, as long as the data source is correct, it will be successful.

B. Use an SQL statement to establish the statement. There are two main sentences:

Sp_addmediaserver 'erp _ db', 'oracle ', 'msdaora', 'erp. com. cn'

Goexec sp_add1_srvlogin @ rmtsrvname = 'erp _ db', @ useself = 'false', @ locallogin = 'sa ', @ rmtuser = 'Scott', @ rmtpassword = 'tigger'

Erp_db indicates the name of the linked server, and erp.com.cn indicates the name of the Oracle data source.

2. Write synchronous SQL

This step is very important. I was not very familiar with it at the beginning. I have been working on it for a long time. Here I will give an example and focus on it:

Select name from erp_db..scott.employee

The following three parts are selected from the linked server. ertdb is the name of the linked server, Scott is the user name, and employee is the table or view object. Remember to write the three parts in uppercase, otherwise, an error occurs. an error is reported during execution. in addition, your user name must have the permission to access this object. If you do not have the permission, it will not work.

Iii. Synchronization Mechanism Establishment:

This mainly refers to how to synchronize and when to execute the SQL statements. You can use these SQL statements as a storage process and call them when you need to synchronize them or make them into a job, execute jobs on a regular basis to synchronize your data.