dba123
asked on
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.
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.
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
http://support.businessobjects.com/library/kbase/articles/c2000757.asp
Regards
Emre
Also this is a great document telling about ODBC architecture for Crystal
http://support.businessobjects.com/communityCS/TechnicalPapers/cr9_odbc_architecture.pdf
Emre
http://support.businessobjects.com/communityCS/TechnicalPapers/cr9_odbc_architecture.pdf
Emre
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
frodoman
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
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
ASKER
>>>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 ?
where can I get it, where can I check to see if it is installed, and how do I reference/use/setup ?
ASKER
>>>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
never used it, don't know how to set this up...where, details, need hand holding down to the novice detail here
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).
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
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
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
ASKER
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?
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?
ASKER
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...
ASKER
>>>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.
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.
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
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
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\ad
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
c:\oracle\ora81\network\ad
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
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Regards
Emre