Using connection string to access Oracle database

[Former Member]

         In Crystal Reports Designer, I select File>>New>>Blank Report and the Database Expert appears.  I click Create New Connection and ODBC(RDO).  I select the radio button next to 'Enter Connection String' and I enter a connection string to attempt to connect to an Oracle Database and I get the message "Logon failed.  Details: Cannot find DSN in connection string"  Is it possible to use the 'Enter Connection String' option to connect to an Oracle database?  I attempted using the following:  User Id=Oracle1;Password=Oracel1PW;Server=10.42.10.16;Direct=True;Sid=POC1;Persist Security Info=True

Thank You

[DellSC · Accepted solution]

1.  Make sure that you have the 32-bit version of the Oracle Client installed - even if the machine you're working on is 64-bit.  Crystal is 32-bit software and will only work with 32-bit database clients.

2.  If you're going to use ODBC, you need to create a 32-bit ODBC connection prior to trying to use it.

3.  Since you're connecting to Oracle, you should try using the Native Oracle connection instead of ODBC - it will be faster and, generally, you'll get better results with it.

-Dell

[Former Member · Accepted solution]

I can connect to my oracle database if I select 'Select Data Source' and pick the Data Source Name from the list.  What I want to do is to be able to select 'Enter Connection String' and provide the Connection String in the text box to create the connection.  I am wondering if this is possible with an Oracle database and what is the proper format of the connection string?

[Former Member · Accepted solution]

I was able to resolve this by creating an incident with SAP.  A support tech pointed out that when scheduling a report from the CMC there is a Database Logon page where I can enter credentials to connect to the database.  When I filled out the values in that page and scheduled the report it ran successfully

[Former Member]

Hi David,

is there any oracle client installed on the PC?

Or Is there an ODBC connection setup on the PC?

regards Alex

[Former Member]

Alex,

Yes there is an oracle client and there is an ODBC connection setup.  I can connect to the Oracle database if I select the Data Source Name: PHC1 Oracle 32 ODBC below.  I want to be able to choose 'Enter Connection String' method to  connect to the Database.   When I use the 'Data Source  Name' method and I save the report to the enterprise folder and try to schedule the report, the report fails. 

With reports using a connection to SQL Server I found that if I use the 'Data Source Name' method and scheduled them to run with CMC they would fail.  When I changed their connections to use the'Enter Connection String' method I was able to schedule successfully.

[Former Member]

Hi,

are you scheduling the report through a Business Objects or crystal report server?

i would assume the ODBC connection fails because it is not setup on the Server.

But why even bother about ODBC , when you have an Oracle client installed.

I guess the same is true for the server.

So as long as  the tnsnames.ora holds your database entry, you should be able to connect.

As Dell mentioned  oracle native connections are faster and more stable.

And you don't need to setup the ODBC connections on all of your servers.

regards alex

[Former Member]

I can connect to my oracle database if I select 'Select Data Source' and pick the Data Source Name from the list.  What I want to do is to be able to select 'Enter Connection String' and provide the Connection String in the text box to create the connection.  I am wondering if this is possible with an Oracle database and what is the proper format of the connection string?