Oracle Data to MS SQL Server 2005

  • Guys,

    I need help in importing data from Oracle database to MS SQL Server 2005. Do any one know of the best way to go about this task? Is there any tool I can use to accomplish thsi task?

     

  • You need to install the Oracle client for whatever version of Oracle you're using and set up linked servers via this client. Once you install the client, you can use SQLPlus from the cmd line to verify connectivity, then set up the linked servers using the information you put in the tnsNames file (Oracle connection file) and use Openquery in SQL Server to pull the data into SQL. It's a complicated process, but I was lucky enough to have some Oracle DBA's on the other side of the wire helping with the initial connectivity.

  • - g8r65  provided the way to connect it.

    - In case you want to migrate from oracle to sqlserver MS provides a migration assistant (Microsoft SQL Server Migration Assistant (SSMA) for Oracle)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I agree with newbie on the Enterprise Manager linked server to oracle. I have used OLEDB MSORA, but sometimes use Oracle OLE. Once you have the link connected, using Query Analyzer with

     

    select *

    into PickATableName

    from openquery(LinkedOracleName, 'Select * From Account where Debt = ''Y'' ')

    Just a heads, up make sure the text inside your single quotes has two ticks '' instead of the double quote" around text items.

  • Fellow SQL-ers,

    I find the Syntax examples in BOL lacking a little.  Here are some notes I have on connection to Oracle from one of our SQL Server instances:

    Step 1: (SQL Server Side) Create a Linked Server.

    Step 2: (SQL Server Side) Create a Linked Server Login. Uses an Oracle User and Password, communicating through TNSNAMES.

    Step 3: (SQL Server Side) Use OpenQuery with Oracle SQL syntax.

    -- Step 1:

    -- Adding linked server (from SQL Server 2000 Books Online):

    /* sp_addlinkedserver [@server =] 'server'

        [, [@srvproduct =] 'product_name']

        [, [@provider =] 'provider_name']

        [, [@datasrc =] 'data_source']

        [, [@location =] 'location']

        [, [@provstr =] 'provider_string']

        [, [@catalog =] 'catalog']

    */

    -- Add a Linked Server 'Ora817Link', connecting to an Oracle 8.1.7.x.x database.

    EXEC sp_addlinkedserver   'Ora817Link',  'Oracle',  'MSDAORA',  'oracle817'

    Step 2:

    -- Adding linked server login:

    /* sp_addlinkedsrvlogin [@rmtsrvname =] 'rmtsrvname'

        [,[@useself =] 'useself']

        [,[@locallogin =] 'locallogin']

        [,[@rmtuser =] 'rmtuser']

        [,[@rmtpassword =] 'rmtpassword']

    */

    -- Add Linked Server Login for 'Ora817Link', using the Scott Oracle User.

    EXEC sp_addlinkedsrvlogin 'Ora817Link', 'FALSE',NULL, 'scott', 'tiger'

    -- Step 3:

    -- Test connectivity and basic rights/permissions.

    SELECT * FROM OPENQUERY

    (Ora817Link,'SELECT sysdate FROM DUAL')

    GO

     

    Results from Query:

    SYSDATE

    -------

    2007-09-06 13:59:33.000

    -- Help on the linked server:

    -- System Stored Procedures to help troubleshoot Linked Servers.

    EXEC sp_linkedservers

    EXEC sp_helpserver

    select * from sysservers

     

     

     

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

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

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