Sqlserver2000 Contact ORACLE11G database for real-time data synchronization

Sqlserver2000 Contact ORACLE11G database for real-time data synchronization

1, prerequisites

I have sqlserver2000 environment, already exist oracle11g environment, prepare these two databases, set up their own access account, between the two need to ping, Telnetport also to be able to pass, The goal is to synchronize the data inside SQL Server itself 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.

Enter "System DSN". Click on the "Join" button, in the pop-up "Select the driver you want to install the data source for" 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.

For example, as seen in 1.png:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbwnozgjh/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70 /gravity/center "/>

Then in the popup interface, set the

Data Source name: This is where the datasource names are. Is the Tnsnames.ora inside the configuration of the data source name, can be used tnsping XXX can test OK data source name.

Description: Descriptive narrative, arbitrary filling

User Name: Users, username connected to Oracle database, such as Scott

Server: Software server, default feel Oracle

Over here. According to your own actual situation, set up for example as seen in 2.png:

Then click "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.

Enter "System DSN". Click the "Join" button, and in the pop-up selection box, select the "Oracle in oradb10g_home3" option in the Select the driver for which you want to install the Data Source list box. Click "Finish" button. For example, as seen in 3.png:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbwnozgjh/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70 /gravity/center "/>

Then the screen pops up again. Set up

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

Description: Descriptive narrative, arbitrary filling

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

User ID: Is the username that connects to the Oracle database. For example, Scott.

For example, as seen in 5.png:

Click on the "Test Connection" button. Enter the Usernamepassword that connects to the Oracle database, assuming that the "Connection successful" prompt will pop up if successful.

Then click "OK" button to complete the creation. In the ODBC data Source Administrator interface, a new test data source is generated, as seen 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 will be able to establish a link server to connect to the Oracle database, go to SQL Server Group (LOCAL) (Windows NT), Server Enterprise Manager console folder, > "Security", "Link Server".

On the right side of the blank form interface, right-click to select "New link Server". Start setting:

In the General tab interface:

(0), define the connection name;
(1). Select a different data source; Specify the program name: Microsoft OLE DB Provider for Oracle;
(2). The product name is optional.
(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 press for example the following format: Uid=username; PWD=USERPASSWD),
The username and password here correspond to the username 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 username and password.

Server Options tab: available by default,

Finally click "OK" button to complete the establishment of the link service, for example, as seen in 7.png:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbwnozgjh/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70 /gravity/center "/>

4. Verify the link server

Access to SQL Server Enterprise Manager console folder, "Windows NT", "Security", "Link Server", "TEST", click on the table, You can see all the table names that the Oracle database user owns in the right-hand form. For example, as seen in 8.png:

5, in SQL Server Query Analyzer form, verify that the link servers

--Run 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 ');

--Change 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 the Enterprise Manager of SQL Server and can be viewed with the help of the SQL Manager for SQL Servers tool. You can do this without using SQL Server's manager. Can be created directly by using DDL statements. DDL statements such as the following are seen:

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. Summary of some error messages

Error 7399:ole DB provider Msdaora ' error.

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

For example, with: 20150310/1.png

The first way to solve this problem:

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

The detailed setup method is as follows: In Enterprise Manager, manage->sql Server agent, right-click menu, Properties, Paging tab Select Connection, use SQL

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

Another way to solve this problem:

When establishing a ODBD data source, do not use the default OracleClient "Oracle in Oradb10g_home3" driver option to use the "Microsoftodbc for Oracle" driver option.

 ----------------------------------------------------------------------------------------------------------------
< Copyrightthere is. textChaptersconsent to be reproduced, but must be linked to the source address, otherwise be held legally responsibleRen!>
Original Blog Address: http://blog.itpub.net/26230597/viewspace-1457700/
Hara Douglas Fir (MCHDBA)
----------------------------------------------------------------------------------------------------------------

Copyright notice: This article Bo Master original articles, blogs, without consent may not be reproduced.

Sqlserver2000 Contact ORACLE11G database for real-time data synchronization