Question Re: Using connection string to access Oracle database in Technology Q&A

Using connection string to access Oracle database

Former Member — Tue, 17 Mar 2015 22:47:52 GMT

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

Re: Using connection string to access Oracle database

Former Member — Wed, 18 Mar 2015 11:32:13 GMT

Hi David,

is there any oracle client installed on the PC?

Or Is there an ODBC connection setup on the PC?

regards Alex

Re: Using connection string to access Oracle database

DellSC — Wed, 18 Mar 2015 13:45:59 GMT

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

Re: Using connection string to access Oracle database

Former Member — Wed, 18 Mar 2015 18:27:13 GMT

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.

Re: Using connection string to access Oracle database

Former Member — Thu, 19 Mar 2015 14:21:32 GMT

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

Re: Using connection string to access Oracle database

Former Member — Fri, 20 Mar 2015 18:01:07 GMT

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?

Re: Using connection string to access Oracle database

Former Member — Fri, 20 Mar 2015 18:01:38 GMT

Re: Using connection string to access Oracle database

Former Member — Fri, 03 Apr 2015 18:02:29 GMT

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