1416336 - Crystal Dynamic Parameter gets error: Failed to open rowset

Symptom

Dynamic parameter in Crystal Report fails with "Failed to open rowset"
Scheduled Crystal Reports List of Values (LOV's) gets error "Failed to open rowset"
Previously, the dynamic parameters and scheduled LOV's were working with no errors.

Environment

Crystal Reports XI R2
BusinessObjects Enterprise XI R2
Reporting Database: Oracle 10g with Oracle native connection
Unix

Reproducing the Issue

In Crystal Reports, refresh a report with a Dynamic Parameter and get error "Failed to open rowset"
In Business View Manager, schedule a List of Values. The schedule fails with error "Failed to open rowset"

Cause

When a Crystal Reports List of Values is generated, it creates SQL that includes Select Distinct and Order by. When run from a Windows client, the sql returns with no errors. On the unix server, however, the SQL generated gets Oracle error 1791: not a SELECTed expression
Cause: There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.

On the Oracle 10g server, case insensitive search was enabled by setting the following variables:

NLS_COMP=LINGUISTIC
NLS_SORT=BINARY_CI

Prior to the case insensitive search being set, the LOV's and dynamic parameters were working correctly.

Resolution

Change the Oracle variables to the following:

NLS_COMP=ANSI
NLS_SORT=BINARY_CI

Use the Oracle Wire Protocol odbc driver on the Unix server.

Keywords

ora-1791, Select Distinct, Order by, DCP, , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

SAP Crystal Reports XI