Professional Web Applications Themes

Inserting data from Oracle to SQL Server 2000 - Microsoft SQL / MS SQL Server

Hi, I have an accounting system in SQL Server that has some time sharing data with a system which uses an Oracle database. The Oracle DBA's gave me an account/pass, table structures so, I can send them data directly into their tables. I did this by simply setting up the Oracle connection (Oracle provider for OLEDB) in the package who make the transfer. Quite easy. Now, I need this to work in reverse direction, because my application will be receiving data from two (may three) differents Oracle databases (separate applications and servers), I set up some tables, accnts/pass, permissions, and ...

  1. #1

    Default Inserting data from Oracle to SQL Server 2000


    Hi,

    I have an accounting system in SQL Server that has some time sharing data
    with a system which uses an Oracle database.
    The Oracle DBA's gave me an account/pass, table structures so, I can send
    them data directly into their tables.
    I did this by simply setting up the Oracle connection (Oracle provider for
    OLEDB) in the package who make the transfer. Quite easy.

    Now, I need this to work in reverse direction, because my application will
    be receiving data from two (may three) differents Oracle databases (separate
    applications and servers), I set up some tables, accnts/pass, permissions,
    and gave to them, so, they could connect to my MsSQL 2000 database.

    But they say that they will have to investigate how to do this and even see
    if this is feasible. What I did to connect to Oracle was so simple (install
    the Oracle client, setup the connection string and the transform data task),
    that I almost can't believe it could be that hard. What do they need to
    install/buy ?

    I know this question is most likely to be asked in Oracle's newsgroups, but
    SQL Server is my area, and I think many of you here could have an idea about
    what options they have to connect directly to a SQL Server database. (One of
    the Oracle's server runs in NT (9i) and another in Unix(8i) ).


    Thanks much in advance,







    Tim Guest

  2. #2

    Default Re: Inserting data from Oracle to SQL Server 2000

    Can't you set this up so that you can pull the data from the Oracle servers
    using DTS (or Linked Servers or whatever) instead of them pushing it to you?

    The story is more complex when coming from the Oracle side for a number of
    reasons. For example, Microsoft includes DTS as part of every SQL Server
    edition. With Oracle, you have to buy the Internet Developer Suite to get
    their equivalent Oracle Warehouse Builder tool. And while Microsoft
    includes an OLE DB for Oracle provider as part of MDAC (and thus on every
    operating system) how would you connect from the Oracle/Unix system to SQL
    Server? The last time I investigated what Oracle offered as a competitor to
    SQL Server's Linked Servers, you'd have to purchase some kind of separate
    connectivity solution to make it work.

    So the bottom line is that SQL Server comes with everything you need to
    connect to Oracle servers while Oracle makes you purchase additional
    software to connect from Oracle to SQL Server. That's why your co-workers
    on the Oracle side have to go do a bunch of investigation to see if it's
    even feasible.

    --
    Hal Berenson, SQL Server MVP
    True Mountain Group LLC


    "Tim Conner" <com> wrote in message
    news:%phx.gbl... 
    (separate 
    see 
    (install 
    task), 
    but 
    about 
    of 


    Hal Guest

  3. #3

    Default Re: Inserting data from Oracle to SQL Server 2000


    "Tim Conner" <com> wrote in message
    news:%phx.gbl... 
    (separate 
    see 
    (install 
    task), 
    but 
    about 
    of 



    On your SQLServer add tnsnames.ora entries for each oracle database.
    eg

    ORACLE1 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oradb04.mydomain.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = ORACLE)
    )
    )



    Then just create linked servers to each of the oracle servers, the Data
    Source is just the tnsnames alias for the server.

    To add the linked server run (use the same name for the linked server as the
    tnsnames alias).


    EXEC sp_addlinkedserver
    server = 'ORACLE1',
    srvproduct = 'Oracle',
    provider = 'MSDAORA',
    datasrc = 'ORACLE1'

    EXEC sp_addlinkedsrvlogin 'ORACLE1', 'false', NULL, 'scott', 'tiger'



    Then you can use the linked servers in stored procedures or ad-hoc queries,
    etc.

    eg

    select * from ORACLE1..SCOTT.EMP



    You can then select data, insert it into SQL tables, join it, etc.

    David


    David Guest

Similar Threads

  1. Replies: 4
    Last Post: February 5th, 12:09 PM
  2. Replies: 7
    Last Post: September 11th, 07:03 PM
  3. data encryption in sql server 2000
    By Dinesh.T.K in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 11:08 PM
  4. Replies: 0
    Last Post: December 23rd, 08:47 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139