Post reply

How to Connect To Oracle 9i

  • February 15, 2006 at 11:11 pm

    #111902

    Can someone show me how to connect from SQL Server 2000 to Oracle 9i?

    I have installed the Oracle 9i Client on the server but what do I do now in terms of a Linked Server?

    I tried to find some info on the net but found nothing constructive especially wit the paramaters that you have to configure when createing the linked server!

    Any one?

    Kindest Regards,

  • February 15, 2006 at 11:35 pm

    #621261

    What have you tried so far ?

    Can you TNSPing the Oracle instance you're trying to connect to ?

    Have you read the BOL example code under the sp_addlinkedserver topic:

    C. Use the Microsoft OLE DB Provider for Oracle

    This example creates a linked server named LONDON Mktg that uses the Microsoft OLE DB Provider for Oracle and assumes that the SQL*Net alias for the Oracle database is MyServer.

    USE masterGO-- To use named parameters:EXEC sp_addlinkedserver   @server = 'LONDON Mktg',   @srvproduct = 'Oracle',   @provider = 'MSDAORA',   @datasrc = 'MyServer'GO

  • February 15, 2006 at 11:38 pm

    #621262

    Do the following way, you may get the solution.

    -> Open SQL Server Enterprise Manager

    -> Select SQL Server -> goto Security folder -> select Linked Server

    -> right click on it, select New Linked Server.

    -> Window will be opened.

    -> Give the Linked server name.

    -> Select Server Type: Microsoft OLE DB Provider for Oracle

    -> Product Name: MSDAORA

    -> Data Source: <Net8Connection Name> (eg: Ora10gR2)

    -> Goto the Next Tab (i.e. Security)

    -> Select Local SQL Server user (eg: sa)

    -> Remote User: scott

    -> Remote Password: tiger

    then press OK.

    Kishore.P

     

  • February 15, 2006 at 11:44 pm

    #621263

    Ok.

    How do I find out the SQL*Net alias for the Oracle database?

    The Oracle Server name is ORS5330 and the Database Name is FPS.

    How do I incorporate that in the SQL*Net?

    Kindest Regards,

  • February 15, 2006 at 11:58 pm

    #621264

    Do you have an Oracle DBA that can help you ?

    I've only worked with Oracle 7 & 8 connected to Sql Server, but I don't even try any SQL connectivity until I've gotten a TNSPing to the Oracle instance. This typically involves configuring a file named TNSNAMES.ORA in the Oracle network admin folder, which usually involves an Oracle DBA to provide the config params.

  • February 16, 2006 at 12:21 am

    #621266

    I will ask the Oracle DBA however, this is the entry in the TNSNames file.

    I can connect to it via Oracle EM, so the entry is valid.

    ORS5330 -Prod =

      (DESCRIPTION =

        (ADDRESS_LIST =

          (ADDRESS = (PROTOCOL = TCP)(HOST = ORS5330)(PORT = 1521))

        )

        (CONNECT_DATA =

          (SERVICE_NAME = FPS)

        )

      )

    Kindest Regards,

  • February 17, 2006 at 8:36 am

    #621691

    You'll reference ORS5330-Prod as the Data Source in the linked server properties if you use the Microsoft OLE DB Provider for Oracle. We use the same string for the Product Name, and leave the Provider String blank. I believe that the Oracle provider's configuration is similar.

    Microsoft's provider does not have any options, but the Oracle provider does. I believe that the defaults should be sufficient for most applications.

Viewing 7 posts - 1 through 6 (of 6 total)

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