Sqlserver2000 Connect oracle11g database for real-time data synchronization

Sqlserver2000 Connect oracle11g database for real-time data synchronization

1, prerequisites

already has the sqlserver2000 environment, already exists the ORACLE11G environment, prepares these two databases, establishes the respective access account, needs to ping between the two, the Telnet port also must be able to pass, The goal is to automatically synchronize the data inside SQL Server into the Oracle database. A link server (linked server) can be implemented in SQL Server.

2. Establishing an ODBC data source above SQL Server

There are 2 ways: the Microsoft ODBC for Oracle option and the Oracle INORADB10G_HOME3 option

2.1, "Microsoft ODBC for Oracle" drive mode

Go to Data sources (ODBC), Administrative Tools, Control Panel, and double-click Open.

Go to "System DSN", click "Add" button, in the pop-up "Select the driver you want to install the data source" select box, select the "Microsoft ODBC for Oracle" option in the list box, click "Finish Button".

PS : Do not select Install here Oracle when the client generates the " Oracle in Oradb10g_home3 "this drive.

As shown in the 1.png:

Then in the popup interface, set the

The data source name: Here is the datasource names, is the data source name configured in Tnsnames.ora, you can use tnsping XXX can test OK data sources name.

Description: Description, feel free to fill in

User name: The user name that connects to the Oracle database, such as Scott

Server: Software server, default to Oracle

Here, depending on your situation, the settings are as shown in 2.png:

Then click on the "OK" button to generate a new ODBC data source.

2.2, "Oracle in Oradb10g_home3" drive options

Go to Data sources (ODBC), Administrative Tools, Control Panel, and double-click Open.

Go to "System DSN", click "Add" button, select the "Oracle in oradb10g_home3" option in the "Select the driver for which you want to install the data source" list box in the pop-up selection box, click "Finish Button". As shown in the 3.png:

After that, the interface that pops up is set

Data Source Nam: Just enter a name, this name will be used in the back of SQL Server

Description: Description, feel free to fill in

The TNS Service name: In the drop-down list is the TNS information configured in Oracle, which is empty if you start without configuration. After you choose a TNS name, you can click Test Connection to verify the connection is not.

User ID: Is the username that connects to the Oracle database, such as Scott.

As shown in the 5.png:

Click the "Test Connection" button to enter the user name password to connect to the Oracle database, and if successful, the "Connection successful" prompt box will pop up. Then click "OK" button to complete the creation, in the "ODBC Data Source Manager" interface will generate a new test data source, as shown in 6.png:

3, in Enterprise Manager, create a link server that connects to the Oracle database

Once you have created the ODBC data source earlier, you can begin establishing a link server that connects to the Oracle database, and go to SQL Server Group (LOCAL) (Windows NT), Server Manager, console directory. > "Security", "Link Server". On the right side of the blank window interface, right click on select "New link Server" to start setting:

In the General tab interface:

(0), define the connection name;
(1), select another data source; Specify the program name: Microsoft OLE DB Provider for Oracle;
(2), the product name is not filled;
(3), the data source specifies the name of the data source defined in ODBC just now;
(4), the provider string is pressed
Fill in the following format: Userid=username; password=userpasswd
(or by the following format: Uid=username; PWD=USERPASSWD),
The user name and password here correspond to the user name and password in the Oracle database to which you want to connect.

In the Security tab:

Settings are made in this security context and entered into Oracle's database user name and password.

Server Options tab: available by default,

Finally click on the "OK" button to complete the establishment of the link service, as shown in 7.png:

4. Verify the link server

Enter SQL Server Enterprise Manager, console directory, "Windows NT", "Security", "Link server," "TEST", click Table, You can see all the table names that the Oracle database user owns in the right window, as shown in 8.png:

5, verify the link server in the Query Analyzer window of SQL Servers

--Execute Query

SELECT * from OPENQUERY (TEST_ORCL, ' select* from SCOTT. Zzz_test ');

--Data entry

Insert INTO OPENQUERY (TEST_ORCL, ' SELECT *from SCOTT. Zzz_test ')

VALUES (2, ' B ');

SELECT * from OPENQUERY (TEST_ORCL, ' select* from SCOTT. Zzz_test ');

--Delete data

DELETE from OPENQUERY (TEST_ORCL, ' SELECT *from SCOTT. Zzz_test ')

WHERE id=2;

SELECT * from OPENQUERY (TEST_ORCL, ' select* from SCOTT. Zzz_test ');

--Modify data

UPDATE openquery (TEST_ORCL, ' SELECT * from SCOTT. Zzz_test ') SET name= ' A2 ' WHERE id=1;

SELECT * from OPENQUERY (TEST_ORCL, ' select* from SCOTT. Zzz_test ');

6. View the DDL statements of the link server in the background

It is not visible in SQL Server Enterprise Manager, and can be viewed through the Administrator for SQL Servers tool, which you can do without the manager of SQL Server and can be created directly using DDL statements, as follows:

EXEC master.dbo.sp_addlinkedserver @server = N ' Test_orcl ', @srvproduct = ' Oracle ', @provider = ' msdaora ', @datasrc = ' test_ ORCL ', @location = ', @provstr = ' uid=username; Pwd=paxxx ', @catalog = ' GO EXEC master.dbo.sp_serveroption @server =n ' test_orcl ', @optname = ' collation compatible ', @opt Value = ' false ' GO EXECmaster.dbo.sp_serveroption @server = N ' Test_orcl ', @optname = ' collation name ', @optvalue = ' null ' G O exec master.dbo.sp_serveroption @server =n ' test_orcl ', @optname = ' data access ', @optvalue = ' true ' GO EXEC master.dbo.sp _serveroption @server =n ' test_orcl ', @optname = ' Connect Timeout ', @optvalue = ' 0 ' GO EXEC master.dbo.sp_serveroption @serv Er =n ' test_orcl ', @optname = ' query timeout ', @optvalue = ' 0 ' GO EXEC master.dbo.sp_serveroption @server =n ' TEST_ORCL ', @op Tname = ' rpc ', @optvalue = ' false ' GO EXEC master.dbo.sp_serveroption @server =n ' test_orcl ', @optname = ' rpc out ', @optvalu E = ' false ' GO EXEC master.dbo.sp_serveroption @server =n ' test_orcl ', @optname = ' Use remote CollatIon ', @optvalue = ' true ' GO EXEC [email protected] = N ' Test_orcl ', @locallogin = NULL, @useself = ' false ', @rmtuser = N ' username ', @rmtpassword = ' password ' GO

7, some error message summary

Error 7399:ole DB provider Msdaora ' error.

OLE DB Error Tracking [ole/db Provider ' Msdaora ' idbinitialire::initializereturned 0x80004005:] .

such as: 20150310/1.png

The first of these solutions:

The SQL Server connection problem in the agent cannot be authenticated with Windows and must be logged in with the sysadmin.

Here's how to do this: in Enterprise Manager, manage->sql Server agent, right-click menu, Properties, Paging tab Select Connect, use SQL

Server Authentication ", then" Apply "and" OK ".

Second Solution:

Do not use the "Oracle in Oradb10g_home3" driver option for the default Oracle client when establishing the ODBD data source, using the "Microsoftodbc for Oracle" driver option.

Sqlserver2000 Connect oracle11g database for real-time data synchronization