How to connect to Oracle Database from SQL Server instance and query the Oracle Database?
Linked Server is the solution for this.
A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.
A linked server definition specifies the following objects:
· An OLE DB provider : is a DLL that manages and interacts with a specific data source
· An OLE DB data source : identifies the specific database that can be accessed through OLE DB
Basics of Linked Server Configuration:
Advantages of Linked Server:
· The ability to access data from outside of SQL Server.
· The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
· The ability to address diverse data sources similarly.
· Install Oracle Client
· Configure tnsnames.ora
(ADDRESS = (PROTOCOL = TCP)(HOST = oraDBsrvr.siva.com)(PORT = 60000))
(SERVICE_NAME = stg01.siva)
Validate tnsnames.ora entry
On Command Prompt
Here we are going to use Microsoft ODBC for Oracle as the OLE DB Provider.
The STG21.SIVA is going to be OLE DB Data Source pointing to the Oracle Database STG01.SIVA
Open up ODBC
Select System tab
Select Microsoft ODBC for Oracle
Click OK, OK
Connect to SQL Server Management Studio
Right click select new Linked Server
Right click select Test connection
Just run the sample query
exec ('select user, sysdate from dual') at STG21_SQLSVR54
Output look like;
SQLSVR07 2012-09-25 01:37:02.000