Oracle and SQL Server


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Oracle and SQL Server

  1. #1
    Myqu Guest

    Oracle and SQL Server



    Hi ,

    I am trying to connect to Oracle from Sql server as I need to use a few tables
    from it on an ongoing
    basis. How do I go about connecting to Oracle from Sql server.

    Thanks much
    Myqu

  2. #2
    Neil Pike Guest

    Re: Oracle and SQL Server

    Myqu - assuming it's SQL 7.0 then lookup "linked servers" in the SQL 7.0
    books-online

    No Oracle example below, but you get the idea...
    #
    > I am trying to connect to Oracle from Sql server as I need to use a few tables
    > from it on an ongoing
    > basis. How do I go about connecting to Oracle from Sql server.


    Q. Are there any examples of heterogeneous data queries from SQL 7 to other
    sources?

    A. Here are a variety of examples for several different datasources. Note that
    you will have to change filenames, drives, regions etc. as necessary for your
    environment :-

    1. Selecting from an Excel spreadsheet using OpenRowSet. Here, c:\ramsql7.xls
    is a spreadsheet (note we haven't specified the extension). sheet1 is a sheet
    within the spreadsheet - note the trailing $.

    SELECT * FROM OpenRowSet
    ('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=c:\ramsql7', 'SELECT *
    FROM [sheet1$]')
    as a

    2. Selecting from an Access linked server database via Jet. The Access
    database is at c:\msd\invent97.mdb

    print 'add Jet 4.0 Invent'
    -- Clear up old entry
    if exists(select * from sysservers where srvname = N'INV')
    exec sp_dropserver N'INV', N'droplogins'
    go
    -- create linked server
    exec sp_addlinkedserver @server = N'INV', @srvproduct = '', @provider =
    N'Microsoft.Jet.OLEDB.4.0', @datasrc = N'c:\msd\invent97.mdb'
    go
    -- setup default admin login for Access
    exec sp_addlinkedsrvlogin @rmtsrvname = N'INV', @useself = N'FALSE',
    @locallogin = NULL, @rmtuser = N'admin', @rmtpassword = N''
    go
    -- Lists all tables in the linked server
    exec sp_tables_ex N'INV'
    go
    -- Now select from a table in the Access db called INVENT
    select * from INV...INVENT
    go

    3. DB/2 accessed via Star SQL Driver with SNA 4.0.

    print 'add DB2 LinkedServer'
    if exists(select * from sysservers where srvname = N'DB2')
    exec sp_dropserver N'DB2', N'droplogins'

    exec sp_addlinkedserver @server = 'DB2', @provider = 'MSDASQL', @srvproduct
    = 'StarSQL 32',
    @location = 'DBT1', @datasrc = 'DB2IBM'
    exec sp_addlinkedsrvlogin @rmtsrvname = 'DB2', @locallogin = 'sa', @useself
    = 'false',
    @rmtuser = 'HDRUSER' ,@rmtpassword = 'SQL7'
    go

    -- test to see is catalog is accesible
    sp_tables_ex N'DB2'

    -- create view to see if select works
    create view V007MUNI as select * from DB2..T1ADM007.V007MUNI
    go
    select * from V007MUNI
    go

    4. DBASE IV

    print 'add DBase IV LinkedServer'
    if exists(select * from sysservers where srvname = N'DBFs')
    exec sp_dropserver N'DBFs', N'droplogins'

    EXEC sp_addlinkedserver
    'DBFs',
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'F:\DBFs',
    NULL,
    'dBase IV'
    GO

    exec sp_addlinkedsrvlogin
    @rmtsrvname = 'DBFs',
    @useself = false,
    @locallogin = NULL,
    @rmtuser = NULL,
    @rmtpassword = NULL
    go

    SELECT * FROM DBFs...test
    go

    5. Visual FoxPro. Using a FoxPro DBC file to group the DBF files. ODBC DSN
    pre-defined called FOX using the Microsoft Visual FoxPro Driver 6.01.8440.01

    -- FOX using Visual FoxPro Database file .DBC
    print 'add FOXSERVER'
    if exists(select * from sysservers where srvname = N'FOXSERVER')
    exec sp_dropserver N'FOXSERVER', N'droplogins'

    exec sp_addlinkedserver @server=N'FOXSERVER',
    @srvproduct ='',
    @provider = N'MSDASQL',
    @datasrc=N'FOX'

    exec sp_addlinkedsrvlogin @rmtsrvname=N'FOXSERVER',
    @useself = N'FALSE',
    @locallogin = NULL,
    @rmtuser = N'',
    @rmtpassword =N''


    exec sp_tables_ex N'FOXSERVER'

    select * from [FOXSERVER].[D:\SQL\FOX\TESTDATA.DBC]..[customer]

    6. FoxPro using plain DBF files in a directory. Using an ODBC system DSN
    (Called DBF) using the Microsoft Visual FoxPro Driver 6.01.8440.01

    -- DBF using plain .DBF files
    print 'add DBFSERVER'
    if exists(select * from sysservers where srvname = N'DBFSERVER')
    exec sp_dropserver N'DBFSERVER', N'droplogins'

    exec sp_addlinkedserver @server=N'DBFSERVER',
    @srvproduct ='',
    @provider = N'MSDASQL',
    @datasrc=N'DBF'

    exec sp_addlinkedsrvlogin @rmtsrvname=N'DBFSERVER',
    @useself = N'FALSE',
    @locallogin = NULL,
    @rmtuser = N'',
    @rmtpassword =N''

    exec sp_tables_ex N'DBFSERVER'

    select * from [DBFSERVER].[D:\SQL\DBF]..[country]

    7. FoxPro using installable Jet 3.51 ISAM drivers.

    print 'add FOXDBC using Jet 3.51'
    if exists(select * from sysservers where srvname = N'FOXDBC')
    exec sp_dropserver N'FOXDBC', N'droplogins'
    exec sp_addlinkedserver 'FOXDBC', 'Jet 3.51', 'Microsoft.Jet.OLEDB.3.51',
    'c:\sql\fox', NULL, 'FoxPro 3.0'
    exec sp_addlinkedsrvlogin @rmtsrvname = N'FOXDBC', @useself = N'FALSE',
    @locallogin = NULL, @rmtuser = NULL, @rmtpassword = NULL
    exec sp_helplinkedsrvlogin N'FOXDBC'
    exec sp_tables_ex N'FOXDBC'

    ===

    v1.04 1999.04.27
    Applies to SQL Server versions : 7.0, 2000
    FAQ Categories : Application Design and Programming
    Related FAQ articles : n/a
    Related Microsoft Kb articles : n/a
    Other related information : n/a
    Authors : Neil Pike
    Neil Pike MVP/MCSE. Protech Computing Ltd
    (Please reply only to newsgroups)
    SQL FAQ (428 entries) see
    forumsb.compuserve.com/vlforums/UK/default.asp?SRV=MSDevApps (sqlfaq.zip - L7 -
    SQL Public)
    or www.ntfaq.com/sql.html
    or www.sql-server.co.uk
    or www.mssqlserver.com/faq



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center