• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3150
  • Last Modified:

Trouble setting up ODBC for Oracle for use with Crystal

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
dba123
Asked:
dba123
  • 7
  • 6
  • 4
  • +1
1 Solution
 
ebolekCommented:
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
 
ebolekCommented:
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
 
ebolekCommented:
Also this is a great document telling about ODBC architecture for Crystal

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

Emre
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
frodomanCommented:
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
 
ebolekCommented:
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
 
dba123Author Commented:
>>>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
 
dba123Author Commented:
>>>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
 
frodomanCommented:
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
 
frodomanCommented:
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
 
ebolekCommented:
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
 
dba123Author Commented:
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
 
dba123Author Commented:
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
 
dba123Author Commented:
>>>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
 
ebolekCommented:
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
 
frodomanCommented:
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
 
dba123Author Commented:
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
 
GhostModCommented:
PAQd, 500 points refunded - asker posted solution

GhostMod
Community Support Moderator
0
 
dba123Author Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now