Oracle Linked Server SEtup Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Oracle Linked Server SEtup Error

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-02-08 : 16:18:07

I have SQL 2005 on my system and would like to connect to Oracle 10g as a linked server.This is the error mssg Im getting
OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink".

This is what has been done so far
1) Installed Oracle Client for 10g on my machine
2) The C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN has 2 files [tnsnames] and [sqlnet]
3) Contents on tnsnames is as below
gp =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cs.org)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = gp2)
)
)
4) Executing tnsping gp returns

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-FEB-2
010 16:02:10
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\10.2.0\client_1etwork\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = cs.org)(PORT = 1526))) (CONNECT_DATA = (SERVICE_NAME = gp2
01)))
OK (20 msec)

5) Execute sqlplus login/123@gp returns

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 8 16:04:35 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

6) In mngt studio, used following script to create linked server
EXEC sp_addlinkedserver @server = 'TestOraLink', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle', @datasrc = 'gp'

7) Followed by
EXEC sp_addlinkedsrvlogin 'TestOraLink', false, 'login', 'login', '123'

8) To test execute
SELECT * FROM OPENQUERY(TestOraLink, 'select * from test_table2')

Can someone please guide me on what is it that Im missing ? Thanks

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 08:03:40

In case any help my (now very dated!) LinkedServer SProc call for Oracle looks like this:


EXEC sp_addlinkedserver @server = 'MyOracleServer', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'OracleServerName'
-- , @location=NULL, @provstr=NULL, @catalog=NULL

I would also suggest trying OPENDATASOURCE to establish some connections parameters - that may be quicker "round each loop iteration" whilst trying to get a connection going.

www.connectionstrings.com may help with getting the parameters right for that.

(You could even use OPENDATASOURCE instead of a LinkedServer, if you can get OPENDATASOURCE working but not a Linked Server)

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-02-09 : 11:25:13

I dont see any difference in your sp_addlinkedserver and mine.
I need to have the linked server as we would be nedding access on a constant and daily basis to the Oracle data.

Could you tell me the steps you have taken to get the linked server up and running ?

Thanks

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-02-09 : 11:37:56

If I remove the 'Allow In Process' on OraOLEDB.Oracle provider.I get the following error when executing
SELECT * FROM OPENQUERY(TestOraLink, 'select * from test_table2')

Error MSG
OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink".

If 'Allow In Process; is selected,I receive the following message
OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 11:39:39

"I dont see any difference in your sp_addlinkedserver and mine"

My Provider is different.

I didn't know what your datasrc represented, so my example was intended to clarify how I have used that.

"Could you tell me the steps you have taken to get the linked server up and running ?"

Sorry, long time since I last had to do it.

I remember installing OraClient (can't remember the correct name, sorry), and I have a recollection of fiddling with tnsnames for a significant amount of time - trial & error

I presume there is some Client Tool that you can use to prove that TNSNAMES is set up right?

If that worked I would be inclinded to try OPENDATASOURCE next. That will allow a more conventional connection string to be used, which ought to follow the path that testing with (Oracle) client tools did - that would establish that SQL Server can talk to your Oracle box.

That then only leaves the parameters to LinkedServer Sproc and the UserID / Password for sp_addlinkedsrvlogin

Only other thing that occurs to me is I think we have to do:

SELECT * FROM OPENQUERY(TestOraLink, 'select * from OracleInstance.test_table2')

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-02-09 : 11:47:03

I shall try the OPENQUERY and update my results

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 11:58:22

I don't think the errors indicate that is the problem, but I'll cross my fingers!

Here's the TNSNAMES.ORA we are using:


# TNSNAMES.ORA Network Configuration File: D:\oracle\ora81\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

XXXXXX =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 1.2.3.4)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = yyyyyy)
    )
  )

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-02-09 : 13:31:16

The hostname in tnsnames.ora, can it be an ip address ?

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 13:48:48

The "HOST = 1.2.3.4" bit in mine is an IP address, the XXXX and YYYY are not - but I don't know if they can be.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-02-09 : 14:08:42

I started off from scratch again. Reinstalled the Oracle Client to ensure I did not miss any steps from my previous setup.Upon completion, I updated my hostname in tsnames.ora to ipaddress.

Here is my error mssg this time
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "TestOraLink".

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 14:58:02

I was hoping someone else more knowledgeable might chip in ... my knowledge is very limited, but I can clearly remember the amount of Trial and Error that was involved. Which is Pants really - a decent error message that pointed at the problem would save folk hours & days of time ...

Only thought I have is that we used:
@provider = 'MSDAORA'

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-02-10 : 09:32:04

Im gonna try to use 11i client instead, to see if that makes any diff.Thanks

andyhallam
Starting Member

9 Posts

Posted - 2011-01-25 : 07:58:59

I have the same problem.
Did you manage to resolve it - I'm pulling my hair out here...

I am running 64bit windows with SQL SERVER 2008.
11.2 Oracle on windows (all on the same machine)

Using OraOLEDB.Oracle as MSDAORA is not shown in the Providers list???

Am doing -

exec sp_addlinkedserver @server='andyh', @srvproduct='Oracle', @provider='OraOLEDB.Oracle', @datasrc='MYORACLE'

exec sp_addlinkedsrvlogin @rmtsrvname='andyh', @useself='false', @locallogin='sa', @rmtuser='myuser', @rmtpassword='xxxx'

tnsping MYORACLE works fine.

- I execute the below
select * from andyh..SCOTT.EMP

- I get
OLE DB provider "OraOLEDB.Oracle" for linked server "andyh" returned message "ORA-01017: invalid username/password; logon denied".

The @rmtuser and @rmtpassword are correct - I can connect via sqlplus using these values no problem.

Help

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-01-25 : 09:12:58

Hi andyhallam,

Its been sometime since this problem, what I do remember is installimg 11i client did solve my problem. I was using sql 2005 at that time.

andyhallam
Starting Member

9 Posts

Posted - 2011-01-25 : 09:42:06

I have now posted this issue on the 2008 forum.

No more replies on this thread please *but* please check out the 2008 forum post.

thanks.

Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources