RE: Problems with Linked Server

SSCrazy

Points: 2803

February 4, 2010 at 12:11 pm

dweil (2/4/2010)
Try changing “HOST = MyServer” to ”HOST = ip address”.
Oracle might not be resolving the host address correctly.
It that does not work you might try
1.renaming the tnsnames file,
2.make sure that tnsping does not work,
3.bring up Oracle Net manager and recreate the tnsnames file
4.try tnsping again
5.try sqlplus again
6. If sqlplus works try the linked server again
Sometimes Oracle is very sensitive to a space or tab in the wrong place in the tnsnames file.
Also there might be some control character in the tnsnames file that you do not see.
David Weil

I changed the Server to the IP Address and it worked fine as below

Microsoft Windows [Version 5.2.3790]

C:\Documents and Settings\lislj>cd

C:\>E:

E:\>cd E:\Oracle\product\10.2.0\client_1etwork\admin

E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>tnsping MyDB

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 04-FEB-2

010 18:11:51

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)

(HOST = IPAddress)(PORT = 1535))) (CONNECT_DATA = (SERVICE_NAME = MyDB)))

OK (30 msec)

E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>sqlplus usnme/pwd@MyDB

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 4 18:12:36 2010

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL>

I tried it on the LinkedServer in Management Studio and it brought this error

OLE DB provider "OraOLEDB.Oracle" for linked server "MyLinkServer" returned message "ORA-12541: TNS:no listener".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "MyLinkServer".

I rebooted the Server before trying the LinkServer again.

How can it connect through sqlplus but not through Management Studio?