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

SAP Knowledge Base Article - Public

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                                                                                                                   

or

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 BusinessViews Manager , Problem

Product

SAP Crystal Reports XI