How to set up oracle 11g as linked server in sqlserver 2005

Question

How to set up oracle 11g as linked server in sqlserver 2005

haribabu_rk

Valued Member

Points: 58
More actions
July 9, 2009 at 9:30 pm

#136303

Hi
I have an Oracle11g DB on a LINUX(Oracle Enterprise Linux) platform and I need to create a linked server in my SQL Server 2005 DB. The SQL Server is on a 32-bit Windows Server 2003 Standard Edition and I've installed ODAC 11g Beta 11.1.0.7.10 components.
How to create Linked Server in Sqlserver 2005 for Oracle11g?
Can any one please tell me the steps to create linked server.
thanks
hari

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

here's the code I use to add a linked server; there's no difference between a linked server for 10G vs 11G as far as I know.

--#################################################################################################

--Linked server Syntax for Oracle 10G

--#################################################################################################

DECLARE @server sysname,

@srvproduct nvarchar(256),

@provider nvarchar(256),

@datasrc nvarchar(100),

@location nvarchar(100),

@provstr nvarchar(100),

@catalog sysname,

@sql varchar(1000)

--add an access Database as a linked server

SET @server = N'MyOracle' --this is your Alias you will call it from SQL

SET @srvproduct = N'Oracle'

SET @provider = N'ORAOLEDB.Oracle' --optionally 'MSDAORA' to use the MS driver

SET @datasrc = N'SFMN10G' --this is the SID, straight from your TNSNAMES

set @provstr = ''

EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr

-- exec sp_dropserver AccessDb

exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle',

@useself = N'FALSE',

@locallogin = 'sa',

@rmtuser = N'CHANGE_ME',--oracle username

@rmtpassword = 'myoraclePass' --oracle password

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323547 More actions · Answer 2

oops duplicate reply from me somehow...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!