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',
@datasrc='igrid101/orcl'

After this, map your SQL login to Oracle Login

sp_addlinkedsrvlogin @rmtsrvname='OrclGridDB',
@useself='false',@locallogin='sa',@rmtuser='grid',@rmtpassword='grid'

Now you can access the table's data as below:
  Select * FROM [OrclGridDB]..[GRID].[PO_LINE_CLEARANCE_HEAD_TAB]
Here grid being the schema name.

Comments

Popular posts from this blog

Using External Content Types with Stored Procedures with Input Parameters

HTTPS Site InfoPath Issue : You do not have permissions to access a web service that provides data required for this form to function correctly

Windows 7 Blue Screen because of ConnectiFy