Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2002
    Location
    East Coast
    Posts
    5

    Question Unanswered: Pulling information from an oracle database into my SQL Server database

    I have been tasked with pulling information from a clients Oracle database in a stored procedure to pass out information. This needs to be a simple stored procedure but i am not sure as to how to point it to pull from a different server and use the ODBC to connect and pull this information. Any suggestions??
    Last edited by Aycex; 03-28-02 at 11:19.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Have you looked at using sp_addlinkedserver
    MCDBA

  3. #3
    Join Date
    Mar 2002
    Location
    East Coast
    Posts
    5

    YES

    I took a look at this but was told by my supervisor we are not creating a linked server he just wants to use the ODBC to go from SQL to Oracle, and get the info need. So i am basically looking for a script on how to access the ODBC to perform this.

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Then look at OPENROWSET. Since you are saying that you want to access Oracle from MS-SQL, I'm assuming that this means T-SQL and not a program (VB,C++,Perl).
    MCDBA

  5. #5
    Join Date
    Mar 2002
    Location
    East Coast
    Posts
    5

    Re: T-Sql

    I am actually wanting to query the Oracle database directly from a stored procedure. Yes it is T-Sql.

  6. #6
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    If you don't (or can't) use Linked Servers then look at OPENROWSET. All you need is the ODBC driver for Oracle.

    From BOL
    Use OPENROWSET with an object and the OLE DB Provider for ODBC
    This example uses the OLE DB Provider for ODBC and the SQL Server ODBC driver to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized with a provider_string specified in the ODBC syntax used by the ODBC provider, and the catalog.schema.object syntax is used to define the row set returned.
    Code:
    USE pubs
    GO
    SELECT a.*
    FROM OPENROWSET('MSDASQL',
       'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
       pubs.dbo.authors) AS a
    ORDER BY a.au_lname, a.au_fname
    GO
    MCDBA

Posting Permissions

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