There can be a plethora of scenarios in which we need to connect to different databases, get data into MS SQL Server and update data in remote Server. SQL Server has a linked server feature through which we can link to other database servers. In this article, I will explain what Linked server is, the advantages of Linked servers and how we can link Microsoft SQL database to Oracle database and connect to Oracle database with full steps. Additionally, we will learn to write a sample OPEN Query to fetch data from the Oracle database to SQL Server database after connection succeeds.
What are Linked Servers?
This feature is available on SQL Server and Azure SQL Managed Instance. It allows SQL Server Database Engine and Azure SQL managed instance to read data and execute commands against remote database servers, for instance, Oracle database server. After the Linked server configuration it enables SQL server Database engine to execute Transact-SQL statement. Using this feature, we can fetch data and update it in other databases without developing custom application.
Linked Servers have the following components,
- An OLE DB provider
- An OLE DB data source
Linked Servers Benefits,
- Allow to access data from outside of SQL Servers
- It has the ability to perform distributed queries including select, update as well as other commands and transactions across heterogeneous data sources.
- It has the ability to address different databases similarly.
Prerequisites
- Oracle Client
- MS SQL Server
- SQL Server Management Studio
To link SQL Server to an Oracle database, firstly we need to install Oracle client on the machine where we have SQL Server and SQL Server Management Studio. I have assumed that we have already installed the Oracle client.
Then, follow the below steps,
Open SQL Server Management Studio(SSMS), go to Server Objects and then Linked Servers.
Right click on Linked Servers and then click on New Linked Server as depicted below.
Give Linked Server Name as per your naming convention. Select Oracle Provider for OLEDB in Provider.
In Data Source use the same name which is used in the tnsnames.ora file (with or without the domain name). You can omit the Provider string and Catalog.
In the security section, provide security details as per your organization.
If you have setup in your Oracle database with remote login and password, then select "Be made using this security context" as shown below and provide Remote login and password.
Finally, click on OK.
Now, Linked Server is created; right click on it and click on Test Connection to verify it.
If your test does not succeed then go to OraOLEDB.Oracle as depicted below .
You will get below screen. Mark the checkbox for Allow inprocess as illustrated below.
Try again to test the connection to verify it. You will be successfully able to connect the oracle Server.
You can now simply run the select query and see the data in SQL Server Management Studio.
Sample query to fetch data from Oracle database to MS SQL is provided below.
- SELECT * FROM OPENQUERY(LinkedServerName,'select t.* from OracleDatabaseName.TableName t')
Cheers -- we succeeded in fetching data from Oracle to SQL Server.
Conclusion
Th-s article has described Linked servers and the benefits as well as necessary steps to connect SQL Server to the Oracle Database. Furthermore, this article explained how to fetch data from Oracle to MS SQL Server database. Besides this, we can perform other queries and commands too. After linking two database we can use data from Oracle database and use it in ASP.NET application, use it for reporting purposes and fulfill many more requirements. I hope the article has given you an idea of Linked Servers and will help you to connect these two diverse databases successfully.
Reference