Showing posts from May, 2013

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 PlusOracle Call InterfaceOracle NetOracle Connection ManagerOracle Services For Microsoft Transaction ServerOracle Administration Assistant For WindowsOracle Provider for OLE DBOracle Data Provider for .NetOracle 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',@rmtus…