Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1108
  • Last Modified:

Oracle ODBC connection for Crystal Reports

I need to connect Crystal to an Oracle Database.  I have the DB in the tnsnames file.  I set-up the odbc connection and tested the connection and it is fine,  When I go to connect Crystal I keep getting the following error: ORA12154,  I also noticed that the tnsnames are not populating in the drop-down in the ODBC connection like my other computer.

Please help.
0
nobile00
Asked:
nobile00
  • 8
  • 5
1 Solution
 
nobile00Author Commented:
The connection is also fine in SQL Developer
0
 
mrjoltcolaCommented:
Test without ODBC. See below, change ORA1 to the name of your service/database.

1) tnsping ORA1

2) sqlplus user/pass@ORA1



If these fail make sure your tnsnames.ora does not have a syntax error. sample below/

# Replace CAT with your database instance name
# Replace the ip address with your server's ip address
CAT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CAT)
    )
  )

Open in new window

0
 
nobile00Author Commented:
Tried the above and crystal is still returning a ORA12154

Any reason the tnsnames are not populating in the ODBC connection drop-down.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
nobile00Author Commented:
The pings came back fine.
0
 
nobile00Author Commented:
this is the tnsnames entry:

STAGING =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = icolims-dev-db.lab.iconcr.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ICOLIMS)
    )
  )
0
 
mrjoltcolaCommented:
Do you have multiple ORACLE HOMEs?
0
 
nobile00Author Commented:
where would I look to find if I have multiple homes?  As far as I know I only have one.
0
 
mrjoltcolaCommented:
If windows, try your registry:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

You also should make sure the user running .NET has privs. If the user that installed Oracle is a different user, that may cause the 2nd user not to see the Oracle libs and config.
0
 
nobile00Author Commented:
only one home listed.

Not sure what you mean about the .net
0
 
mrjoltcolaCommented:
Sorry I got the thread confused. I meant Crystal Reports / ODBC. There is another thread open with similar problem from .NET client.

Try setting ORACLE_HOME in the environment prior to starting Crystal reports.

set ORACLE_HOME=C:\oracle\10.2.0\...

Whatever your home is.


0
 
nobile00Author Commented:
Sorry but how do i do the above?  
0
 
mrjoltcolaCommented:
I was talking about a DOS command prompt, but I don't know Crystal reports.

Check your System environment variables. See that the Oracle bin directory is in the PATH.

Are you using the same user for Crystal Reports as for ODBC
0
 
nobile00Author Commented:
Thanks
Your info as well as a re-install of the client worked.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now