Adding Oracle Linked Server in SQL Server (11G and SQL Server 2008 R2)

Do ensure you install the client components in your machine (from where you would like to connect to Oracle).
Choose Custom Installation of the Oracle Client and pick the following:
  • SQL Plus
  • Oracle Call Interface
  • Oracle Net
  • Oracle Connection Manager
  • Oracle Services For Microsoft Transaction Server
  • Oracle Administration Assistant For Windows
  • Oracle Provider for OLE DB
  • Oracle Data Provider for .Net
  • Oracle Providers for ASP.Net

After this you will the Oledbprovider.Oracle under the Linked Server Options in SQL Management Studio.

After this, 

run the below: (@Server is any name that you provide, @srvproduct has to be Oracle, @provider is the Provider name and @datasrc - is machine name/service name)
exec sp_addlinkedserver @server='OrclGridDB',@srvproduct='Oracle',@provider='OraOLEDB.Oracle',

After this, map your SQL login to Oracle Login

sp_addlinkedsrvlogin @rmtsrvname='OrclGridDB',

Now you can access the table's data as below:
Here grid being the schema name.


Popular posts from this blog

Comparison of Power BI Service vs. Power BI Report Server vs. SQL Server Reporting Services

Multi Factor Authentication, Conditional Access for Power BI

An object in the SharePoint administrative framework "SPDatabaseServiceInstance Name=SharePoint" could not be deleted. SPWebServiceName=WSS_Administration SPWebService