January 29, 2010 at 3:21 am
I am trying to create a Linked Server to an Oracle db.
I have installed Oracle Database 10g Client Release 2 and then installed Oracle 10g Release 2 ODAC
I changed the TNSNAMES.ora to this
MyDatabase =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ServerName)(PORT = 1535))
)
(CONNECT_DATA =
(SERVICE_NAME = MyDatabase )
)
)
I then Rebooted the server and started to configure the Link Server using OraOLEDB.Oracle
I did this below
Linked Server - HSP
Provider - Oracle Provider for OLE DB
Product Name - Oracle
Data Source - ServerName taken from the HOST in TNSNAMES.ora
Under Security I chose Be made using this security context and entered the username and password
When clicked ok it created a Linked Server, but when I ran SELECT TOP 10 * FROM HSP ..SYS.HELP to test the link it returned this error.
OLE DB provider "OraOLEDB.Oracle" for linked server "HSP" 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 "HSP".
I have tried to google it and have had no joy in getting it to work. How can I get it working?
January 29, 2010 at 7:44 am
No listener means that the Oracle server side could not be contacted from the client. Have you tested the connection via sqlplus or tnsping?
January 29, 2010 at 7:49 am
DNA_DBA (1/29/2010)
No listener means that the Oracle server side could not be contacted from the client. Have you tested the connection via sqlplus or tnsping?
No, Whats SQLPlus, how can I do this, also how can I tnsping?
January 29, 2010 at 10:25 am
if you've installed the oracle client you should have both tnsping and sqlplus.
at a command prompt, type
[font="Courier New"]tnsping MyDatabase[/font]
this should return some info about the connection and show OK if it connected.
then type
[font="Courier New"]sqlplus s/s@MyDatabase [/font]
this should return invalid username/password - this will show the client has tried to connect to the server.
MyDatabase is the name you have set up in tnsnames.ora
February 1, 2010 at 4:00 am
DNA_DBA (1/29/2010)
if you've installed the oracle client you should have both tnsping and sqlplus.at a command prompt, type
[font="Courier New"]tnsping MyDatabase[/font]
this should return some info about the connection and show OK if it connected.
then type
[font="Courier New"]sqlplus s/s@MyDatabase [/font]
this should return invalid username/password - this will show the client has tried to connect to the server.
MyDatabase is the name you have set up in tnsnames.ora
Ok, this I tried that and this is my results
C:\Documents and Settings\lislj>tnsping MyDatabase
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 01-FEB-2
010 10:55:20
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
E:\oracle\product\10.2.0\db_1etwork\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = MyServerName)(PORT = 1535))) (CONNECT_DATA = (SERVICE_NAME = MyDatabase)))
OK (0 msec)
C:\Documents and Settings\lislj>sqlplus s/s@MyDatabase
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 1 10:56:45 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name: MyUsername
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name: MyUsername
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
What does any of this mean?
February 1, 2010 at 5:23 am
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
What does any of this mean?
It means you are not able to connect to Oracle from the SQL Server machine. Check the TNSNAMES.ora file to make sure you have the right connection settings. Your Oracle DBA should be able to help you here.
February 1, 2010 at 5:27 am
Looks like you've got both Oracle 10g client and 11g client installed. The 10g client knows where to find the database but the 11g client doesn't - can you check the tnsnames.ora under where Oracle 11g is installed.
February 1, 2010 at 7:22 am
DNA_DBA (2/1/2010)
Looks like you've got both Oracle 10g client and 11g client installed. The 10g client knows where to find the database but the 11g client doesn't - can you check the tnsnames.ora under where Oracle 11g is installed.
I checked it under E:\app\lislj\product\11.1.0\client_1\Network\Admin and there was nothing there. I copied the tnsnames.ora & sqlnet.ora from the 10g and saved it here. I then tried tnsping MyDatabase & sqlplus s/s@MyDatabase and got the same results as before.
Where can I go from here?
February 2, 2010 at 6:57 am
jez.lisle (2/1/2010)
DNA_DBA (2/1/2010)
Looks like you've got both Oracle 10g client and 11g client installed. The 10g client knows where to find the database but the 11g client doesn't - can you check the tnsnames.ora under where Oracle 11g is installed.I checked it under E:\app\lislj\product\11.1.0\client_1\Network\Admin and there was nothing there. I copied the tnsnames.ora & sqlnet.ora from the 10g and saved it here. I then tried tnsping MyDatabase & sqlplus s/s@MyDatabase and got the same results as before.
Where can I go from here?
Where can I go from here?
February 2, 2010 at 10:37 am
hmmm.....
I just want to see if the client is connecting to the database as this will use the listener and prove the connection details are correct - can you start a command prompt, change to the 10g bin directory and run sqlplus from there?
February 3, 2010 at 9:01 am
DNA_DBA (2/2/2010)
hmmm.....I just want to see if the client is connecting to the database as this will use the listener and prove the connection details are correct - can you start a command prompt, change to the 10g bin directory and run sqlplus from there?
Ok, this is the result doing samething on the 10g drive
E:\Oracle\product\10.2.0>tnsping MyDB
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-FEB-2
010 15:55:47
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
E:\oracle\product\10.2.0\db_1etwork\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = MyServer)(PORT = 1535))) (CONNECT_DATA = (SERVICE_NAME = EGR
TST4)))
OK (50 msec)
E:\Oracle\product\10.2.0>sqlplus s/s@MyDB
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Feb 3 15:56:41 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: usrname
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name: eg_sp
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
E:\Oracle\product\10.2.0>
What are your thoughts?
February 4, 2010 at 2:09 am
Just to help with things, this is the website I found some instructions from
http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/">
http://www.ideaexcursion.com/2009/01/05/connecting-to-oracle-from-sql-server/
February 4, 2010 at 3:50 am
I been reading the weblink again that I attached earlier, and realised that I had made an error and had previously installed 10g & 11g clients. I uninstalled it all and started again. This time I only have 10g installed.
I follwed the instructions and still in Managment Studio I cant connect to the Linked Server even though it is in the list. Then I tried the tnsping & sqlplus in Command Prompt. I kept getting the same errors as previous until I did a bit of research on the sqlplus. I then changed to sqlplus username/password@MyDB and I had success by connecting.
This below is the success of Command Prompt.
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\lislj>cd
C:\>E:
E:\>cd E:\Oracle\product\10.2.0\client_1\NETWORK\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 10:39:21
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
E:\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 = MyServer)(PORT = 1535))) (CONNECT_DATA = (SERVICE_NAME = MyDB)))
OK (0 msec)
E:\Oracle\product\10.2.0\client_1\NETWORK\ADMIN>sqlplus usrne/pwd@MYDB
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 4 10:39:52 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
How can I now connect through SQL Server Managment Studio?
February 4, 2010 at 9:34 am
Could the path not be set up correctly?
Can you tnsping from c:\ or E:\?
Also try renaming sqlnet.ora.
Sqlnet.ora in not necessarily needed and it might just not be making it to tnsnames.ora.
From your post it looks like it is going to sqlnet.ora.
David Weil
February 4, 2010 at 10:18 am
dweil (2/4/2010)
Could the path not be set up correctly?Can you tnsping from c:\ or E:\?
Also try renaming sqlnet.ora.
Sqlnet.ora in not necessarily needed and it might just not be making it to tnsnames.ora.
From your post it looks like it is going to sqlnet.ora.
David Weil
The path is set where the Oracle folder is and also where the tnsnames.ora is held
I have tried removing the sqlnet.ora and I end up with same results as I did in my last post
tnsping from the C:\ shows the results below
C:\Documents and Settings\lislj>tnsping MyDB
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 04-FEB-2
010 16:50:36
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
E:\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 = MyServer)(PORT = 1535))) (CONNECT_DATA = (SERVICE_NAME = MyDB)))
OK (0 msec)
C:\Documents and Settings\lislj>sqlplus usnme/pwd@MyDB
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 4 16:51:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name: usnme
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
C:\Documents and Settings\lislj>
on your post what do you mean, when you say
From your post it looks like it is going to sqlnet.ora.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply