Thursday, September 06, 2012

How to connect to Oracle Database from SQL Server instance and query the Oracle Database?

 

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:

clip_image001

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.

Pre-requisite

· Install Oracle Client

· Configure tnsnames.ora

Example

STG21.SIVA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraDBsrvr.siva.com)(PORT = 60000))

)

(CONNECT_DATA =

(SERVICE_NAME = stg01.siva)

)

Validate tnsnames.ora entry

On Command Prompt

Tnsping STG21.SIVA

Notes:

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

clip_image003

Select System tab

clip_image005

Click Add

Select Microsoft ODBC for Oracle

clip_image007

Click Finish

clip_image009

clip_image011

Click OK, OK

Connect to SQL Server Management Studio

clip_image013

Right click select new Linked Server

clip_image015

clip_image017

clip_image019

clip_image021

Click OK

Test Connection

Right click select Test connection

clip_image023clip_image025

Validation:

Just run the sample query

exec ('select user, sysdate from dual') at STG21_SQLSVR54

Output look like;

USER SYSDATE

SQLSVR07 2012-09-25 01:37:02.000

No comments: