How to connect to Oracle using Linkserver in SQLServer

SQLServer provides Linkserver to connect homogeneous or heterogeneous data sources in different databases. The following figure shows how to connect to Oracle:

SQL Server provides Linkserver to connect homogeneous or heterogeneous data sources in different databases. The following figure shows how to connect to Oracle:

The premise of connecting to Oracle is to install the Oracle Client on the SQL Server. The Oracle Client is as follows:

After the installation is complete, modify the corresponding tnsnames file to connect to the corresponding data. The directory where the file is located:

Oracle installation path \ product \ 10.2.0 \ client_1 \ NETWORK \ ADMIN

Connection string format:

QAS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.10.181) (PORT = 1527 ))
)
(CONNECT_DATA =
(SERVICE_NAME = QAS)
)
)

Start SQL Server Management Studio, expand to Server Objects --> Linked Server, right-click the item, select New linked Server, and enter the Link Server information.

After the input is complete, the following information is displayed:

SQL Server provides two methods to connect to Link Server:

Select * from openquery (QAS, 'select * from sapr3.mseg ')
Select top 100 * from [QAS]... [SAPR3]. [MSEG]

Note:

1) The second method should be case sensitive.
2) indexes cannot be used in either of the two methods, and the use of large volumes of data is not supported.