ORA-12541: TNS:no listener". (.Net SqlClient Data Provider)

pdsqsql 411

Hello,
I am having issue with connecting oracle from Sql Server using Linked Server.
I have installed Oracle client and Sql Server on both the same server.
I have tested TNSPING ORCL coming fine

Blockquote
C:\Windows\system32>TNSPING orcl

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-OCT-2020 13:00:03
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\oracle_user\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=orclXDB)))
OK (0 msec)

Blockquote

My Listener status also looks fine for the service

Blockquote
C:\Windows\system32>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-OCT-2020 13:00:24

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER

Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 01-OCT-2020 23:30:11
Uptime 0 days 13 hr. 30 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\oracle_user\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
Listener Log File C:\app\oracle_user\tnslsnr\ORATEST\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "Oracle8" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Blockquote

When I run sqlplus OraUser@ORCL, it's throwing an error:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When I try to create the linked server and test it, getting following error:
ORA-12541: TNS:no listener". (.Net SqlClient Data Provider)

My Linked Server script is:

Blockquote

SE master;

EXEC sys.sp_addlinkedserver @Testta = N'ORCL'
,@srvproduct = N'Oracle'
,@provider = N'OraOLEDB.Oracle'
,@datasrc = N'orcl';

-- Configure the server for remote procedure calls
EXEC dbo.sp_serveroption @Testta = N'ORCL' -- sysname
,@optname = 'rpc out' -- varchar(35)
,@optvalue = N'true' -- nvarchar(128)

EXEC sys.sp_addlinkedsrvlogin @rmtsrvname = N'ORCL'
,@useself = 'false'
,@locallogin = NULL
,@rmtuser = N'OraUser'
,@rmtpassword = N'******';

Blockquote

I have tried few things from the Google but didn't help, trying to follow the steps as other have provided.

Thanks for your help!

Erland Sommarskog 111.1K Reputation points MVP

2020-10-02T21:31:25.87+00:00

Shouldn't yo first talk with people in an Oracle forum to get the issue when you connect with Sqlplus resolved first before you starting dabbling with linked servers?
pdsqsql 411 Reputation points

2020-10-04T16:13:48.737+00:00

Erland,
I agreed with you to check with ORacle expert but i thought this is more going with Sql Linked server so someone has more insight which can help me and thinking that when you are already working with sql server for many years and itm ight someone has already faced this kind of issue.
Erland Sommarskog 111.1K Reputation points MVP

2020-10-04T17:01:12.947+00:00

Yeah, I worked with SQL Server for many years. But I have never worked with Oracle, not even linked servers to Oracle.

However, I spent quite a bit of time of troubleshooting, and I have learnt that it is best to try with errors with the least moving parts first. So troubleshoot all errors that does not involve SQL Server first.
pdsqsql 411 Reputation points

2020-10-04T17:12:26.14+00:00

Erland,
Thanks and I agreed but looking for some expert from this forum who have faced this kind of issue.
But I will definitely check the Oracle forums and i have already spent some time to troubleshoot and somehow didn't get any luck.

6 answers

m 4,271 Reputation points

2020-10-06T08:59:08.72+00:00

Hi @pdsqsql ,

Still having issue

If the issue still exists after you have tested, please don't hesitate to ask professional engineers for help, and they will deal with your problem separately and confidentially: https://support.microsoft.com/en-us/assistedsupportproducts

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.
pdsqsql 411 Reputation points

2020-10-06T18:05:22.987+00:00

Hello,
My Linked Server is working right now, I can Query the Oracle tables but having issue when i am trying to use with SSMA for my Migration project.

when I try to use SSMA for Migration and trying Connect to Oracle and entering parameters, getting Listener error.
Not sure, I am using something wrong in SSMA
Connection to Oracle Failed: ORA-12541: TNS: No Listener

I am using following:

Provider: OLEDB Provider

Mode: Standard

Server name: ORatest

Server Port: 1521

Oracle SID: ORCL

User name: ORaUser

Password: ******

Thanks for your help!

Alexander Ivanov 491 Reputation points

2020-10-15T00:06:45.73+00:00

Hi @pdsqsql ,

Is "ORCL" a SID or a service name? In standard mode in SSMA you can only provide SID, not a service name. I'd suggest you to try Connection String mode, where you can better express your environment configuration.

Regards,
Alex.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

ORA-12541: TNS:no listener". (.Net SqlClient Data Provider)

6 answers

Your answer