[Solved] Sql Query Between Different Databases like Oracle and SQL Server in C# [closed]

Question

You will need to install the Oracle Client first. Google “Oracle Database Client windows” to find the download from Oracle’s site.

1) Install the 64-bit package first. (VERY important as there is a bug that messes things up if you install the x86 package first)

2) Change the install path to “C:\oracle\x64”

3) Once installed install the 32-bit package

4) Change the install path to “C:\oracle\x86”

5) Once installation is finish navigate to
“C:\oracle\x86\11.2.0\client_1\network\admin”

Create or edit a file named “sqlnet.ora” in the folder from #5. Put the following in the file:

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

6) Create or edit a file named “TNSNAMES.ora” in this directory. Put in your normal TNS connection string (your Oracle DBA probably knows this). An example is below:

ServerInstanceName =
 (DESCRIPTION=
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL =TCP) (PORT=1529) (HOST=192.168.1.100))
    )
    (CONNECT_DATA=
    (SID=ServerInstanceName)
    )
)

where ServerInstanceName is the SID of your Oracle server

7) Update TNSNAMES.ora with any servers you need to connect to

8) Copy these files to “C:\oracle\x64\11.2.0\client_1\network\admin”

9) Reboot the machine

Now you should have the Oracle client installed and you can set up a linked server for SQL Server to connect with. Open SSMS and connect to your server. Expand Server Objects and then right click “Linked Servers” and click “New Linked Server”

1) Set linked server name to whatever you want to call the server when querying i.e. OracleSvr

2) Change Provider to “Oracle Provider for OLE DB”

3) For Product Name put in “OraOLEDB.Oracle.1”

4) For Data source put in the same value you used for the server name in the TNS file i.e. ServerInstanceName in our example.

5) Go to Security on the left.

6) Set however you want logins to be made. If not sure how to do this google it

7) Go to Server Options on the left.

8) Make sure Data Access, RPC, RPC Out, Use Remote Collation are all set to True.

9) Click OK.

10) Open Server Objects –> Linked Servers –> Providers

11) Right Click OraOLEDB.Oracle and click properties

12) Click Enable under “Allow inprocess” Click OK.

You should now be able to query the server from within SQL server.

select * from OracleSvr..SchemaName.TableName

Accepted Answer