Link server to Oracle

Access Oracle 10 through SQL Server 2005 Express

EXEC master. dbo. sp_addmediaserver

@ Server = n'testlink ',

@ Srvproduct = n' Oracle ',

@ Provider = n'msdaora ',

@ Datasrc = n' Test'

GO

Create a database link under SQLServer.

Connection name: TESTLINK

Oracle data source is TEST

An Oracle client must be installed on the SQL Server machine.

The Oracle data source, the configuration file is in

E: oracleproduct10.2.0client _ 1 NETWORKADMIN

Directory.

Content:

# 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:Only the SQL SERVER EXPRESS and Oracle 10 clients are installed on the local machine.

The Oralce 10 server is installed on another machine.

EXEC

Master. dbo. sp_add1_srvlogin

@ Rmtsrvname = n' test ',

@ Locallogin = NULL,

@ Useself = n'false ',

@ Rmtuser = n'test ',

@ Rmtpassword = n' test123'

GO

This statement sets the default username/password used to access the Oracle database.

In the preceding example, the user name is test and the password is test123.

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

TESTLINK .. TEST. TEST_MAIN.

The first TESTLINK is the database link name.

The second TEST is the solution name of 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 MSDAORA cannot find the Oracle client when the query is executed after the link is created. If Oracle 9/10 is installed on the local machine. This may be caused by permission issues. You need to set it in the operating system.

　　Take the local machine as an example:

Select the directory E: oracleproduct10.2.0client _ 1.

Right-click and select "properties" from the shortcut menu ".

Then, add an EveryOne user to the "Security" option. And set full control.

Then restart the computer.

After the restart, it should be normal.