Connecting to an Oracle server

Question

Connecting to an Oracle server

richardmgreen1

SSChampion

Points: 10351
More actions
January 14, 2020 at 12:56 pm

#3715195

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

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

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

Do you have TNS entry and have you tried to do Tnsping.

This may help: https://www.sqlserverblogforum.com/dba/test-connection-failed-because-of-an-error-in-initializing-provider-oracle-client-and-networking-components-were-not-found/

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

jasona.work SSC Guru Points: 50117 More actions · Answer 2

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 SSChampion Points: 10351 More actions · Answer 3

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 SSChampion Points: 10351 More actions · Answer 4

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 More actions · Answer 5

Glad you have fixed and used EZConnect identifier.

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