Friday , June 21 2024

Heterogeneous Connection From Oracle to SQL Server

You can read this document to access the tables in the MSSQL database using the DBLINK architecture from the Oracle database. Please test in the TEST database first. If you want to connect to other databases or connect to Oracle from other databases, you should read the below articles.

Connect To Oracle From SQL Server

How To Create a Linked Server To Connect To Oracle”,

Connect To PostgreSQL From SQL Server

“How To Create a Linked Server To Connect To PostgreSQL From SQL Server”

Connect to Oracle From PostgreSQL

“Foreign Data Wrapper Between PostgreSQL and Oracle”

Connect to SQL Server From PostgreSQL

“Foreign Data Wrapper Between PostgreSQL and MSSQL”

Downlaod Package

Download the “Microsoft® ODBC Driver 11 for SQL Server® – Red Hat Linux” package using the link below.

The relevant file is transferred to the server where the oracle database is installed with wget/upload.

Verify the Package

The part that writes NOT FOUND is the package we’re going to use. We haven’t install it yet.

Install the Package

Verify the Installation

After the package installation is complete, we will run some commands to access the .ini and config information generated by the related packages.

Check Package Version

Access to Configuration Information

/etc/odbc.ini will be the most commonly used configuration file.

Access to Driver Information

After these checks, we can now use the relevant packages to connect from Oracle to MSSQL.

Configure Heterogeneous Connection From Oracle to SQL Server

We need to write the information of the MSSQL database to be connected to /etc/odbc.ini.

The point to note here is to specify the type of database we have added under [ODBC Data Sources]. If more than one is used, it must be written to the new line.

The information you see under [MSSQL_DB_NAME] is the information used to access the MSSQL database.

You see an example odbc.ini file above.

We will do the following to make [MSSQL_DB_NAME] meaningful by ORACLE.

In order for MSSQL_DB_NAME to be recognized by ORACLE, the critical point here is that the name of the .ora file must match the definition you make in the .ini file and begin with init….

To access this odbc.ini file by the Environment

Connect to MSSQL from Oracle

If all steps are correct, we will be able to connect to the MSSQL database with the isql tool.

NOTE: Password must be written in single quotes (‘). If there is a single quotation mark inside the password, it is a problem.

If there is no problem up to this point, let Oracle Access this service via DBLINK.

We make the configuration for MSSQL look like it’s a service offered by ORACLE.

NOTE: If you are using an Oracle database in the RAC architecture, the corresponding operation must be performed on each NODE.

Then, the LISTENER must be defined.

We previously learned LD_LIBRARY_PATH during package installation.

Create DBLINK on Oracle database

In the USING section we will use the service name we defined in odbc.ini.
Thus, a heteregeneous connection was established from Oracle to SQL Server.

Note: Please note that service names and access names are the most important part.



Leave a Reply

Your email address will not be published. Required fields are marked *