Reference website:
sp_addlinkedserver Summary (Oracle,sql server,access,excel)
64-bit SQL Server communicates with 32-bit Oracle via linked servers
SQL Server Connection Oracle detailed steps
The following is the text
1. The server needs to install Oracle 64-bit client or service side,
When the installation is complete, "oraoledb.oracle" is added to the SQL Server provider, such as
2. Configure the "OraOLEDB.Oracle" property, enable the "Allow in-process"
3. Create a new linked server
4. Fill in the link server name, select Access Interface "Oracle Provider for OLE DB"
5. Fill in the Product name->oracle
6. Data source fill in the service name of the connection address configured by the Oracle client/server side
(Available in Oracle's net Manager
or directly in the view configuration file
"Installation path \product\11.2.0\dbhome_1\network\admin\tnsnames.ora")
7. Fill in the Oracle login account password: Select "Use this security context to establish a connection", choose security
and fill in the Oracle login name and login password
8. Test connectivity to test connectivity to Oracle database
9. Actual use of links
There are many ways to query oracle,sql on SQL Server,
The common syntax is:
SELECT * from OPENQUERY (Ora_link,'SELECT * from USERNAME. TABLE');
Ora_link: Linked server name
Username:oracle Login Name
Reprint please specify the source, by Lazyneal 2017
SQL Server R2 link Oracle