linked server sql to oracle

Question

linked server sql to oracle

sivark1

SSCrazy

Points: 2654
More actions
April 13, 2010 at 4:33 am

#142545

Hi how to do this task
I need to port the data from Oracle to SQL server. I need linkserver betwee my local database (SQL) to new server -abc (Oracle).
Please reply Thanks in advance

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

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

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 1

You need the specific oracle version driver to setup an odbc source on your SQL server.

We have 10g and i found that the only connector that would work was the actiual driver made by Oracle for SQL (the MS one wouldnt translate properly).

HTH

Adam Zacks-------------------------------------------Be Nice, Or Leave

Lowell SSC Guru Points: 323518 More actions · Answer 2

you need to install the oracle client on the Server that is hosting the SQL Server instance.

you'll have to copy/set up the TNS names files just as you would any other new isntallation, so that machine knows where your oracle instances are.

once you can connect from the desktop of the server to your Oracle instance(s) with SQLPlus or SQLDeveloper or whatever oracle tool you normally use, everything would be in place to set up your linked server.

here is an example of creating an oracle linked server; it will not work unless the steps above were completed.

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

--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 a linked server

SET @server = N'MyOracle' --this is your Alias for the Oravle instance

SET @srvproduct = N'Oracle' --never got this to work with any other value

SET @provider = N'ORAOLEDB.Oracle' --which driver to use; valid for v8-v11; optionally 'MSDAORA' to use the MS driver f v8 or 9

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

cet @provstr = ''

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

-- exec sp_dropserver MyOracle

exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle',

@useself = N'FALSE',

@locallogin = 'sa',

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

@rmtpassword = 'NotARealPassword' --oracle password

--list all the tables and their names

EXEC sp_tables_ex MyOracle

GO

select * from MyOracle..CHANGE_ME.GMACT

select * from MyOracle..MFHDS.GMACT

--remove the linked server

EXEC dbo.sp_DropServer 'MyOracle', 'DropLogins'

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!

sivark1 SSCrazy Points: 2654 More actions · Answer 3

Thank you for quick replys Dudes.

Any other suggessions please with link or screenshots to setup this.

Thanks in advance