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.
I changed the Server to the IP Address and it worked fine as below
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\lislj>cd\
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 04-FEB-2
Copyright (c) 1997, 2005, Oracle. All rights reserved.
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)
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 4 18:12:36 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
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?