SQL Server linked servers to Oracle

SQL Server Express Access Oracle 10

EXEC Master.dbo.sp_addlinkedserver

@server = N ' Testlink ',

@srvproduct =n ' Oracle ',

@provider =n ' Msdaora ',

@datasrc =n ' TEST '

Go

This sentence creates a database link under SQL Server.

Connection name is Testlink

Oracle data source is TEST

Requires a client with Oracle installed on the SQL Server machine.

That Oracle data source, the configuration file is

E:oracleproduct10.2.0client_1networkadmin

Table of Contents.

Content is:

# Tnsnames.ora Network Configuration File:e:oracleproduct10.2.0client_1networkadmintnsnames.ora

# Generated by Oracle configuration tools.

TEST =

(DESCRIPTION =

(Address_list =

(address = (PROTOCOL = TCP) (HOST = 192.168.1.210) (PORT = 1521))

)

(Connect_data =

(service_name = ORCL)

)

)

　　Note: This machine installs only SQL SERVER EXPRESS with Oracle 10 clients.

The Oralce 10 server is installed on another machine.

Exec

Master.dbo.sp_addlinkedsrvlogin

@rmtsrvname = N ' TEST ',

@locallogin = NULL,

@useself = N ' False ',

@rmtuser = N ' Test ',

@rmtpassword = N ' test123 '

Go

This sentence is set by default username/password to access the Oracle database.

The example above is the user name test password for test123

　　After the database link is created, you can try the query:

Here's the Testlink. TEST. Test_main.

The first testlink is the database link name

The second test is the scenario name for the test user in Oracle.

Test_main is a table under the test user.

1> SELECT * from Testlink. TEST. Test_main;

2> Go

ID VALUE

--------------------------------------------------

1 One

2 Two

3 Two

(3 rows affected)

　　Note: If the link is created, when the query is executed, the prompt msdaora cannot find the Oracle client. If this machine is Oracle 9/10 installed. Then there is the possibility of a permission problem that needs to be set up in the operating system.

　　Take this machine as an example:

You need to select E:oracleproduct10.2.0client_1 this directory.

right mouse button, pop-up menu, select Properties.

Then, in the security option, add a everyone user. and set Full Control.

Then restart the computer.

Once the reboot is complete, it should be normal.