Trying to create a linked server from SQL 2005 EE to Oracle 9i EE using GUI

Question

Trying to create a linked server from SQL 2005 EE to Oracle 9i EE using GUI

pankaj.baluni

Ten Centuries

Points: 1014
More actions
August 13, 2009 at 5:47 am

#213401

Hello,
I'm trying to create a linked server from SQL 2005 EE to Oracle 9i EE using SQL GUI. But not sure how to do it.
Please give me step by step procedure to follow. I can do it using T-SQL but also want to do it via SQL GUI.
Please suggest.
Rgds,
Pankaj

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

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

I cannot give you step by step because it is very simple you need to go to Oracle site and download the 9i RC2 client for your operating system install it and configure a connection to your 9i server make sure you put your TNSNAMES.ORA file in your local Oracle folder and connect to the 9i server. If you could also download SQL Developer this will help you navigate Oracle almost like SQL Server. Then go to SQL Server Management Studio go to server objects then linked server and configure linked server with the GUI.

This will get you started, Oracle is almost operating system agnostic I have used Oracle running in IBM Iseries, Solaris and HP Unix it is not important because you Windows client let you connect without issues.

Kind regards,
Gift Peddie

Lowell SSC Guru Points: 323518 More actions · Answer 2

hope this helps: i created a linked server via script, then screenshotted the results in the GUI. You said you can create one via script, so you can do the same thing:

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

--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

SET @srvproduct = N'Oracle'

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

SET @datasrc = N'SFMN10G' --this is the SID

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 = 'NotaRealPassword' --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!