Sqlserver2000 connects to the Oracle11G database for Real-time Data Synchronization

Sqlserver2000 connects to the Oracle11G database for Real-time Data Synchronization 1. the prerequisites are that the sqlserver2000 environment already exists and the oracle11g environment already exists. Prepare the two databases and create their own access accounts. ping the two databases, the telnet port must also be accessible to automatically synchronize data in sqlserver

Sqlserver2000 connects to the Oracle11G data warehouse for Real-time Data Synchronization 1. the prerequisites are that the sqlserver2000 environment already exists and the oracle11g environment already exists. Prepare the two databases and create their own access accounts, ping and telnet ports are required between the two to automatically synchronize data in sqlserver

Sqlserver2000ConnectionOracle11GDataLibraryProceedDataReal-timeSynchronization

1. Prerequisites

The sqlserver2000 environment already exists and the oracle11g environment already exists. Prepare the twoDataLibraryTo create an access account. ping and telnet ports are required between the two accounts.DataAutomaticSynchronizationTo oracleDataLibrary. Sqlserver has a link server.

2. Create odbc on sqlserver DataSource

There are two methods: the "Microsoft ODBC for Oracle" option and the "Oracle inOraDb10g_home3" option

2.1, "Microsoft ODBC for Oracle" Driver

Go to Control Panel> Administrative Tools>DataSource (ODBC) ", double-click to open.

Go to "system DSN" and click "add". In the displayed "select the one you want to installDataIn the Driver selection box of the source, select the "Microsoft ODBC for Oracle" option in the list box, and click the "finish" button.

PS: do not select the "oracle in OraDB10g_home3" Driver generated when installing the Oracle client.

As shown in 1.png:

Then, in the pop-up interface, Set

Data Source Name: here isDataSource name, which is configured in tnsnames. oraDataSource Name, which can be tested with tnsping xxxDataSource Name.

Description: Description. Enter it at will.

User Name: User,ConnectionOracleDataLibraryUser name, such as scott

Server: software Server. The default value is Oracle.

As shown in 2.png:

Click "OK" to generate a new ODBCDataSource.

2.2, "Oracle in OraDb10g_home3" Driver options

Go to Control Panel> Administrative Tools>DataSource (ODBC) ", double-click to open.

Go to "system DSN" and click "add". In the pop-up selection box, select "select to install for it ".DataSelect the "Oracle in OraDb10g_home3" option in the driver list box of the source, and click the "finish" button. As shown in 3.png:

In the dialog box that appears, Set

Data Source Nam: enter a name. This name will be used by sqlserver.

Description: Description. Enter it at will.

TNS Service Name: The TNS information configured in oracle is shown in the drop-down list. If you do not configure the TNS, It is null. after selecting a TNS name, you can click test connection to verify it.ConnectionNo.

User ID: YesConnectionOracleDataLibraryUser name, such as scott.

As shown in 5.png:

Click "Test Connection" and enterConnectionOracleDataLibraryIf the Connection is successful, a prompt box "Connection successful" is displayed. Click "OK" to complete the creation.DataThe source Manager interface generates a new TESTDataSource, as shown in 6.png:

3. Create in "Enterprise Manager" ConnectionOracle DataLibraryLink Server

ODBC created earlierDataAfter the source, you can start to establishConnectionOracleDataLibraryThe link Server of the SQL Server, go to the "Console Directory" of the SQL Server Enterprise Manager-> "SQL Server group"-> "(LOCAL) (Windows NT) "->" security "->" link server ". In the blank window on the right, right-click and choose "New link server" to start setting:

(0), definedConnectionName;
(1) select anotherDataSource. The specified program name is Microsoft ole db Provider for Oracle;
(2) the product name is optional;
(3 ),DataSpecifies the source as defined in ODBCDataSource Name;
(4), provide the program string to press
In the following format: UserID = username; PassWord = userpasswd
(Or in the following format: UID = username; PWD = userpasswd ),
The username and password must correspondConnectionOracleDataLibraryUser name and password in.

Set this security contextProceedAnd enterDataLibraryUser name and password.

Default,

Click the button to confirm the creation of the chain service, as shown in 7.png:

4. Verify the link Server

Go to the Enterprise Manager Console Directory> SQL Server group> LOCAL (Windows NT) "->" security "->" link server "->" TEST ", click the table and you will see the oracleDataLibraryAll user-owned tables, as shown in 8.png:

5. Verify the link server in the SQL Server Query analyzer window.

-- Execute Query

SELECT * from openquery (TEST_ORCL, 'select * from scott. ZZZ_TEST ');

-- InputData

Insert into openquery (TEST_ORCL, 'select * from scott. ZZZ_TEST ')

Values (2, 'B ');

SELECT * from openquery (TEST_ORCL, 'select * from scott. ZZZ_TEST ');

-- DeleteData

Delete from openquery (TEST_ORCL, 'select * from scott. ZZZ_TEST ')

Where id = 2;

SELECT * from openquery (TEST_ORCL, 'select * from scott. ZZZ_TEST ');

-- ModifyData

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.

You cannot see it in the Enterprise Manager of sqlserver. You can view it using the SQL Manager 2008 for SQL Server tool. You can create it using ddl statements instead of using the SQL Server Manager, the ddl statement is 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', @optvalue = 'false'GO  EXECmaster.dbo.sp_serveroption @server = N'TEST_ORCL', @optname = 'collation name',  @optvalue= 'null'GO 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 @server =N'TEST_ORCL', @optname = 'query timeout', @optvalue = '0'GO EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL', @optname = 'rpc', @optvalue = 'false'GO EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL', @optname = 'rpc out', @optvalue = 'false'GO EXEC master.dbo.sp_serveroption @server =N'TEST_ORCL', @optname = 'use remote collation', @optvalue = 'true'GO EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname = N'TEST_ORCL', @locallogin = NULL, @useself = 'false', @rmtuser = N'username', @rmtpassword = 'password'GO

7. Summary of some error messages

Error 7399: The ole db Provider MSDAORA reports an error.

OLEDB Error Tracking [OLE/DB Provider 'msdaora 'IDBInitialire: Initializereturned 0x80004005:].

Example: 20150310/1. png

Solution 1:

SQL server in proxyConnectionCannot Use windows authentication. You must use sysadmin to log on,

The specific settings are as follows: Choose Enterprise Manager> Manage> SQL Server proxy> right-click menu> Properties> select on the tab"Connection"->" Use SQL

Server authentication, and then "application" and "OK.

Solution 2:

Create an odbdDataDo not use the "oracle in OraDb10g_home3" Driver option of the default Oracle client when using the "Microsoft for Oracle" Driver option.

Bytes ----------------------------------------------------------------------------------------------------------------
<版权所有，文章任!>
Original blog address: http://blog.itpub.net/26230597/viewspace-1457700/
Original Author: Huang Shan (mchdba)
Bytes ----------------------------------------------------------------------------------------------------------------