Solved

Trouble setting up ODBC for Oracle for use with Crystal

Posted on 2004-09-10
18
3,046 Views
Last Modified: 2008-01-09
I noticed we have an ODBC setup on one of our servers (Windows 2000) that connects to an Oracle Database.  I'm trying to create the same ODBC on my WinXP Pro machine.

When I look at the list of drivers to choose from in create New Datasource window, I see:

Oracle 7
Oracle 8 v3.6
Microsoft ODBC for Oracle

1) I tried to use the Microsoft choice but I get this error:  

"The Oracle(tm) client and networking components were not found.  These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3 (or greater) client software installation.

You will be unable to use this driver until these components have been installed."

I do not support Oracle here so I do not have this software....nor would I know exactly what to download or what CD to use from Oracle.  I don't even know if I should be using this driver.  The database I'm trying to connect to is running Oracle 8

2) I tried using the Oracle 8 v3.6 driver.  In the setup, I put in:

Data Source Name
Server Name
Default User Name

When I try to click the "Test Connect" button, I get the error  "Specific driver could not be loaded due to system error 126(CR Oracle 8 v3.6)

3) My end objective is to create this ODBC on my PC so I can use it with Crystal to build a report.  I need to create this Oracle ODBC first.
0
Comment
Question by:dba123
  • 7
  • 6
  • 4
  • +1
18 Comments
 
LVL 10

Expert Comment

by:ebolek
ID: 12029622
why dont you try using oledb for oracle. ODBC is very flexible and must be good for alot of the datasources including ORacle. However try oledb for oracle too and see what it does for you

Regards
Emre
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12029651
read this article.This articlle shows the oracle odbc dependencies. You could be missing one

http://support.businessobjects.com/library/kbase/articles/c2000757.asp

Regards
Emre
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12029779
Also this is a great document telling about ODBC architecture for Crystal

http://support.businessobjects.com/communityCS/TechnicalPapers/cr9_odbc_architecture.pdf

Emre
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12029813
You can also install the native Crystal ODBC Driver for Oracle - I've had the best luck using that driver in most cases as it seems to have the fewest unexplainable problems.

frodoman
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12029889
In ODBC there are two kinds of drivers
1.native
2.ODBC drivers specialized for specific DBMS

1. They ship with crystal and are the drivers listed under database files, or More Data Sources under Data Explorer, If you want to use native one you can select the Oracle version from here

2. Also you can use specialized drivers. these are the dlls that help you connect to the specialized DBMS. CROR814.dll for example

Regards
Emre
0
 
LVL 1

Author Comment

by:dba123
ID: 12029955
>>>native Crystal ODBC Driver

where can I get it, where can I check to see if it is installed, and how do I reference/use/setup ?
0
 
LVL 1

Author Comment

by:dba123
ID: 12029959
>>>oledb for oracle

never used it, don't know how to set this up...where, details, need hand holding down to the novice detail here
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12029984
Native Driver:  When you add a new odbc connection you'll see it listed as CR Oracle ODBC Driver 4.10 (the number may be different of course).

0
 
LVL 42

Expert Comment

by:frodoman
ID: 12030006
Should be on your Crystal CD.  If not you can find on the net (e.g.: http://www.dynamiclink.nl/htmfiles/rframes/dll-c08.htm ) looking for CROR818.dll
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 10

Expert Comment

by:ebolek
ID: 12030044
did you read this article that I posted.It explains all the odbc drivers and methods that you can use and where to find them. But simply

go to control panel
go to administrative tools
go to odbc data sources
select driver tab
That shows the drivers installed special drivers

Native drivers already come with crystal and that article gives you the names of dlls that should be on the computer

http://support.businessobjects.com/library/kbase/articles/c2000757.asp

Regards
Emre
0
 
LVL 1

Author Comment

by:dba123
ID: 12030088
I installed the client for Oracle 8i, I was able to then get rid of that error when clicking on the Microsoft ODBC for Oracle.  It prompted me for the following info wich is entered correctly:

Data Source Name
Description
User Name
Server

Ok, but the problem I have now (I'm throwing in Crystal here), is that in Crystal, when I try to expend this ODBC in the set location to try to view the tables, I get this error:

ODBC error: [Microsoft][ODBC Driver for Oracle][Oracle] ORA-12514: TNS: Listener could not resolve SERVICE NAME given in connect.

There was no where in the Create new ODBC window for the Microsoft ODBC for Oracle that allowed me to enter a "SERVICE  NAME".  So Crystal must be looking somwhere else for this?
0
 
LVL 1

Author Comment

by:dba123
ID: 12030104
I understand how to navigate in the ODBC Administrator...I create ODBCs all the time for SQL Server.  I just didn't know about the native drivers...so those are just the list that come with XP then is what you are saying in the Select Server TAB?  That is all I needed to know...a simple translation of Native drivers = the ones you see in that TAB.  The ones I see every fricking day.  Do I know that they are "native"  no, I don't...
0
 
LVL 1

Author Comment

by:dba123
ID: 12030198
>>>did you read this article that I posted

Ok, but if you rename these in WINNT, what about if you have installed the Oracle Client....shouldn't it be using those?  Or should it just use Crystal's ODBCs (dlls) since they were made for this purpose....connecting to Oracle.
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12030238
I will try both methods. Fisrt try to do it with the native drivers

Try tpo connect from moredatasources tab under dataexplorer or data tables. If that fails see if the native driver dlls are located in the client

Then if all fails I will try using
Specialized drivers
create an odbc system dsn for oracle and connect to the report using that dsn created from odbc tab under datasources

I will again check if the dlls are there

Regards
Emre
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12030298
The TNS listener error probably just means that you need to update (or create) your name file.  You should have this file:

c:\oracle\ora81\network\admin\tnsnames.ora

Bear in mind it may be slightly different folders so search on the name if you must.  Open this file in notepad (MAKE A BACKUP FIRST IN CASE YOU MAKE A MISTAKE!!!) anD make an entry corresponding to your oracle service name.  For example here's one of the entries from my file for the "orcl" service name:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = orcl))
  )

Obviously the host=xxx.... should be the tcp/ip address of your database - I removed it here for security.  Once you've made this entry, reboot and hopefully that'll clear the TNS listener error message.

frodoman
0
 
LVL 1

Author Comment

by:dba123
ID: 12046671
I resolved it by having to install Oracle 8i client.  Then I used the ODBC called "Oracle ODBC Driver" which I don't believe I saw prior to installing the client.  That ODBC gave me the prompts I needed unlike the others.
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 12048367
PAQd, 500 points refunded - asker posted solution

GhostMod
Community Support Moderator
0
 
LVL 1

Author Comment

by:dba123
ID: 12048403
can you also please redistribute points to all participants equally...they did a good job helping out and they have valid information although it didn't help me, it could help someone else and I want to recognize their contributions.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now