1205178 - Error: "Cannot open data connection failed to retrieve data from database" when Oracle tables and views used in same report

Symptom

A Crystal report uses both tables and views coming from an Oracle database. After adding three or more fields to the report, the report does not refresh and the following error message appears:

"Cannot open data connection failed to retrieve data from database. Details: 08S01. Data Direct ODBC Wire Protocol driver connection dead Database Vendor code -4".

Cause

This behavior is caused by the user's rights in the database.

The join created in the SQL query of the Crystal report is not recognized in Oracle. Therefore the query fails at the database level:

For example Crystal will create a query like:

Select 'emp.empid','emp.empno',' dept_v.deptno', 'dept_v.loc'

from emp inner join dept_v on

'emp.deptno' = 'dept_v.deptno'

Suppose dept_v is view ceated in the database. Though this query is supported at the database level of Oracle however the inner join, right outer join, left outer join and the full outer joins are not getting recognized.

Resolution

To resolve this behavior, complete the following steps:

Install the Oracle client on your computer, and then configure the TNS names.
Check if the users have been granted the proper rights. A part of the resource rights allows the joins to be recognized as presented by Crystal Reports. Ask your DBA to make the changes and check if the query runs properly with the join structure or not and then use in Crystal.

Keywords

Oracle, Crystal Reports,failed to open data connection. , 8201452 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

SAP Crystal Reports XI ; SAP Crystal Reports XI R2