Connecting to an Oracle server

  • richardmgreen1

    SSCrazy Eights

    Points: 9927

    Hi all

     

    We need to pull data from an Oracle server and I'm trying (and failing) to set up the linked server.

    The Oracle that's running is version 12c and I've installed the OLEDB drivers.

     

    I've also got the details for our login/password as well as the server name and port number.

     

    I've tried using the wizard as well as using sp_add_linkedserver.

     

    Using sp_addlinkedserver, the link is created but I can't then use sp_addlinkedsrvlogin to add our local login.

     

    I've googled how to do this and found several links which have suggested using the OraOLEdb.Oracle.

     

    This is what I get when I script the linked as drop/create:-

    EXEC master.dbo.sp_addlinkedserver @server = N'RIS', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'Server:Port/ServiceName'

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RIS',@useself=N'False',@locallogin=NULL,@rmtuser=N'UserName',@rmtpassword='Password'

    The error I'm getting is:-

    Cannot create an instance of OLD DB provider "OraOLEDB.Oracle" for linked server "servername". (Microsoft SQL Server, Error 7302)

     

     

    If I change the drop/create to :-

    EXEC master.dbo.sp_addlinkedserver @server = N'RIS', @srvproduct=N'Oracle', @provider=N'Oracle', @datasrc=N'Server:Port/ServiceName'

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RIS',@useself=N'False',@locallogin=NULL,@rmtuser=N'UserName',@rmtpassword='Password'

    I get a slightly different error:-

    The OLE DB provider "Oracle" has not been registered. (MicrosoftSQL Server, Error 7403)

     

     

    Anyone any ideas on where I'm going wrong?

     

    TIA

     

    Richard

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19026

  • jasona.work

    SSC-Forever

    Points: 49988

    The first question would be, do the Oracle drivers show up in SSMS under Server Object->Linked Servers->Providers?

    If not, then SQL isn't seeing the drivers and thus can't use them.  Additionally, you likely also need to add your Oracle server connection info into the tnsnames.ora file (this may be Oracle driver dependent, I need it as I use the Oracle Data Access Objects installer for the driver.)

  • richardmgreen1

    SSCrazy Eights

    Points: 9927

    Thanks both

     

    I didn't know about the TNS entry or the .ora file.

    I was following the steps on this page:-

    https://docs.microsoft.com/en-gb/archive/blogs/dbrowne/creating-a-linked-server-for-oracle-in-64bit-sql-server

     

    I'll have a look at the tns entry and the .ora file and let you know how I get on.

     

  • richardmgreen1

    SSCrazy Eights

    Points: 9927

    Thanks guys

     

    Got it sorted.

     

    I rebooted the server after the install of the drivers and then used the code from this page:-

    https://docs.microsoft.com/en-gb/archive/blogs/dbrowne/creating-a-linked-server-for-oracle-in-64bit-sql-server

    (edited to match my requirements) and it now works

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19026

    Glad you have fixed and used EZConnect identifier.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

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

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