Oracle ORA-12154: TNS: Could not resolve service n

Question

I am a SQL Server user .

I am on a project that is using oracle (which I rarely use) I need to create an ODBC connection so I can access the some data via MS Access I have a application on my machine called oraHome90. It seems to allow a configuration of something called a listener in a “net configuration utility”, I think that a “Local Net Service Name Configuration” needs to also be done. The IT support gave me this information to set up the ODBC connection . I have tried every combination that I can think of. I can get past a test that successfully passes a test to “login“ to the oracle server database. When I try to create the ODBC connection I get the following error: ORA-12154: TNS: Could not resolve service name.

Assuming that I want to start from scratch and the following information is supposed to allow for me to connect to the database….. Any suggestions or comment ? Note: ultimately the project will have a website .ASP page query the data, but I have to first prove that I can see the data using the ODBC connection via MS Access

Service name: SERVICENAME
HOST = HOST.XYZi.com
User Id: MYUSERID
Password: MYPASSWORD
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

'Oracle Connection
Dim ocst
Dim oconn

ocst = "Provider=OraOLEDB.Oracle;" & _ 
        "Data Source=DATASOURCE;" & _ 
        "User ID=CHIJXL;" & _ 
        "Password=password;" 

set oconn = CreateObject("ADODB.Connection")

Answer 1

If there is a space in the beginning of the tns name define in file tnsnames.ora, then some of the the connectors like odbc may give this error. Remove space character in the beginning.

Answer 2

Arrhhh!! I RAN INTO THIS AGAIN!!!

Just install ToadForOracle in C:\ or any directory without parenthesis in the path.

---

In my case its because I was on a x64 PC and still using the old Oracle 9i with the 32bit drivers!

I am using SQL Reporting Services with an Oracle Database. The problem is the brackets in the path to Visual Studio (BIDS). Oracle doesn't like apps that start in a path with brackets:

RDBMS 10g XE problem with parenthesis in path

So I made a BAT file to open Visual Studio with Progra~2 as the short path name for "Program Files (x86)".

Here is the contents of the BAT file:

rem Progra~2 is short path name for "Program Files (x86)" and works around an Oracle client bug that doesn't like the ()'s in the path
start /B "C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE" "C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"

I name this BAT file StartBIDS.BAT and put it in the directory:

"C:\Program Files\Microsoft SQL Server\Start BIDS.bat"

Then I make a short cut to the BAT file on my Desktop and also my Start Menu and change the ShortCuts icon. This allows me to open TOAD, Visual Studio, BIDS and etc apps that I use to work with Oracle.

Update:

Alternatively make a Junction:

mklink /J "C:\Program Files (x86)\" "C:\Program Files x86\"

Then remove the brackets in the shortcut:

Answer 3

Only restart the SID services. For example you SID name = orcl then all the services which related to orcl should be restart then you problem will be solved

Answer 4

I just spend an hour on this, I'm new to Oracle so i was thoroughly confused..

the situation:

just installed visual studio 2012 Oracle developer tools. When i did this I lost the items in my drop down which contained my TNS entries in TOAD. I was getting this error from Visual studio AND TOAD!! WTH! so i added the environmental Variable TNS_ADMIN under "ALL USERS" with the path to my .ora file (which i now worked fine because it worked until I broke it). Toad picked up that change. Still Visual Studio wouldn't give me any love... still getting same error. THEN, i added the environmental Variable TO MY USER VARIABLES.. VIOLA!!

ENSURE THE ENVIRONMENTAL VARIABLES ARE SET FOR THE SYSTEM AND THE USER

Answer 5

I had this problem because of a typo in the filename tsnames.ora instead of tnsnames.ora

Answer 6

Hours of problems SOLVED. I had installed the Beta Entity Framework for Oracle and in in visual studio 2010 MVC 3 project I was referencing under the tab .NET the Oracle.DataAccess ... This kept giving me the "Oracle ORA-12154: TNS: Could not..." error. I finally just browsed to the previous Oracle install under c:\Oracle\product.... using the old 10.2.0.100 version of the dll. Finally it works now. Hope it helps someone else.