If you want to fetch data from Oracle to SQL Server, you can use Linked Servers features in SQL Server Database. Linked Server is like a database link (Dblink).
Installation steps
- Install Oracle Client on SQL Server database server
- Restart SQL Server database service
- Configure OraOLEDB.Oracle provider in SQL Server database
- Create user for remote connection in Oracle database
- Create new linked server in SQL Server database
- Add remote login for connect Oracle database in SQL Server
- Query Example
Install Oracle Client on SQL Server database server
You can find Oracle client application on Oracle.com. You can install Oracle client application after download. I will not tell how to install.
Restart SQL Server database service
You can restart SQL Server database service on services.msc
Configure OraOLEDB.Oracle provider in SQL Server database
After Oracle client install, you have to find Oracle provider in SQL Server database Linked Servers as OraOLEDB.Oracle
OraOLEDB.Oracle properties configure like below;
Create user for remote connection in Oracle database
SQL> create user oraconnuser identified by oraconnuser;
SQL> grant create session to oraconnuser;
SQL> grant select on table1 to oraconnuser;
Create new linked server in SQL Server database
exec sp_addlinkedserver @LinkName, @SrvProduct, @Provider, @ConnectionString
exec sp_addlinkedserver ‘ORALINK’, ‘Oracle’, ‘OraOLEDB.Oracle’, ‘OraIP:1521\DBNAME’
Add remote login for connect Oracle database in SQL Server
exec sp_addlinkedsrvlogin @LinkName, @useself, @LocalLogin, @OraUser, @OraPassword
exec sp_addlinkedsrvlogin ‘ORALINK’, ‘FALSE’, NULL, ‘oraconnuser’, ‘oraconnuser’
Query Example
select * from openquery(ORALINK,’select * from table1′)
where table1column = ‘example’