Learn how to a build a cloud-first strategyRegister Now


creating odbc from access to oracle

Posted on 2012-08-14
Medium Priority
Last Modified: 2012-08-14
we get" TNS:Listener was not given the service_name in connec_data"  We are trying to create an odbc from serverA (where access database sits) to ServerB (Oracle server).  Will use ODBC to pull data from ServerA to ServerB.   Any inputs?
Question by:Nana Hemaa
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 750 total points
ID: 38292136
Are you using the Oracle ODBC drivers?
Have you configured the Oracle client's tnsnames.ora file to point to the correct database?

What values did you provide when you created the DSN in the ODBC configuration?

Author Comment

by:Nana Hemaa
ID: 38292231
Yes they are using oracle drivers.  They are trying to get to my Oracle server B.  
This is how they configured the ODBC on ServerA (where access database sits)

Data Source Name = ServerB
TNS Service Name  = Orcl     (database name on serverB)
UserID                    = Username on ServerB

--Have you configured the Oracle client's tnsnames.ora file to point to the correct database?

not sure
I added this to my tnsnames.  

      (ADDRESS = (PROTOCOL = TCP)(HOST = serverA(PORT = 1521))
      (SERVICE_NAME = )
LVL 35

Assisted Solution

johnsone earned 750 total points
ID: 38292293
In the ODBC connection, the service name has to match the name in the tnsnames.ora file.  If you change you entry in tnsnames.ora to this:

      (ADDRESS = (PROTOCOL = TCP)(HOST = serverA)(PORT = 1521))
      (SERVICE_NAME = Orcl)

You should be closer to what you need.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

by:Nana Hemaa
ID: 38292426
They corrected  the database source name  on the ODBC driver and they are good.
LVL 71

Expert Comment

ID: 38292454
You guys know that we do not need tnsnames.ora anymore (since 10g), by using EazyConnection strings instead? In ODBC the TNS entry would then be

There is nothing to gain by having to rely on tnsnames.ora in this particular case, it is just more work. Of course that does not apply to Oracle Clients prior 10g, which still need that file.

Author Comment

by:Nana Hemaa
ID: 38292549
I agree.  As  soon as they configured the ODBC correctly everything started working ok for them. I deleted the my extra entry in the tnsnames.ora  Thanks for the clarification. I was wondering...

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month20 days, 18 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question