Post reply

linked server sql to oracle

  • 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

  • April 13, 2010 at 4:41 am

    #1150596

    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

  • April 13, 2010 at 4:46 am

    #1150599

    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!

  • April 13, 2010 at 10:28 pm

    #1151125

    Thank you for quick replys Dudes.

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

    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