Hello folks. i recently experienced scenario where the two environments have to be linked up for direct communication. so i found the following process to link up MS SQL to Oracle.
Note: The following technologies are considered in this tutorial:
1- Oracle 11 g (IDE: SQL Developer)
2- Microsoft SQL Server 2012
1) In windows
first open sqlnet.ora file
under the folder Program Files (X86)-> Oracle Developer Tools-> network
->admin
2) Change SQLNET.Authentication_Services = (NTS) to SQLNET.Authentication_Services = (NONE)
3) Now open SQL Server
4) In server objects goto Linked Server-> Providers and on OraOLEDB.Oracle right click.
Set
the following properties:
Enable: a)Dynamic Parameter b)Nested queries c)Allow Inprocess d)Supports ‘Like’ operator
5) Now rightclick on LinkedServer and select “New Linked Server” option
6) For "New Linked Server
- Give a name to the server.
- Select Other data source
- In provider select OLE DB Provider for Microsoft Directory Services
- In Product Name enter the name of the provider of Oracle (under LinkedServer->Providers) . Here OraOLEDB.Oracle.
- In DataSource enter the Oracle data source. Here “localhost:1521/xe”
- In
Provider string enter the entire connection string for Oracle:
Here “User ID=rizvi; Password=123; Data Source=localhost:1521/xe; Pooling=false;”
7) In Security page:
Select Option “Be made using the login’s current
security context
8) Data Fetching:
a.Check the table data in oracle
b.Check the same data in SQL server (In
MSSQL query oracle data with Adhoc query option of OpenRowSet
Example
MSSQL query for Oracle:
select * from
openrowset('OraOLEDB.Oracle','xe';'rizvi';'123',
'select * from hmis_tri_counter');
That is all folks for creating a linked server. Happy querying.
Take care.
