SBL-DAT-00500 and SBL-EAI-04451: There were more rows than could be returned. Please refine your query to bring back fewer rows.
Reason:
By default any business component query with ForwardBackward mode can fetch up to 10000 records at a time. If any query (generated by eScript or Workflow or any other way) returns more than 10000 records, Siebel throws this error ‘There were more rows than could be returned.’
How to fix:
Below we have mentioned 3 solutions to fix this error. You could choose one as per your requirement.
Solution 1: Use ExecuteQuery with ForwardOnly mode
This is the best solution if it fulfills your requirement. Oracle also suggests not to use ‘ForwardBackward’ mode with ExecuteQuery if it is not absolutely required. Instead of ‘ForwardBackword’, use execute query with ‘ForwardOnly’ mode that does not limit on fetching records.
Solution 2: Refine query
If you have to use ‘ForwardBackward’ mode to meet the requirement, try to add few more conditions so that Siebel fetches less than 10000 records at a time.
If there is an absolute need of fetching more than 10000 records using ‘ForwardBackward’ query mode, then only go for solution 3.
Solution 3: Change ‘DSMaxFetchArraySize’ parameter value
DSMaxFetchArraySize is a subsystem parameter that determines the number of records a business component query with ‘ForwardBackword’ mode can fetch at a time. By default, DSMaxFetchArraySize has value ‘0’ that means business component can return only 10000 records in ForwardBackward query mode. It does not restrict the number of records in ForwardOnly execution mode. If you want to get all rows from ForwardBackward query, set DSMaxFetchArraySize parameter value as -1 (negative 1). But you do it only when you don’t have any workaround because setting DSMaxFetchArraySize to -1 has impact on application performance due to large memory use. Even Siebel Object Manager could crash because of memory exhaustion.
To change this parameter value, follow below steps:
- Login into Siebel application as Administrator
- Navigate to Administration-Server Configuration > Enterprises
- Under ‘Profile Configuration’ tab, search for the profile ‘Server Datasource’
- In the Profile Parameters list, click on ‘Advanced’ button to get all advanced profile parameters
- Query on Alias field for DSMaxFetchArraySize and set the value to -1
- Restart Siebel Server services
Beside ‘DSMaxFetchArraySize’, you check ‘Maximum Cursor Size’ property at Business Component level also. This property also restricts the number of records that Siebel CRM can fetch from server. If ‘Maximum Cursor Size’ property contains any value, it overrides MaxCursorSize parameter, defined in the configuration file. This parameter is applicable only on Oracle or DB2 database.
– Set ‘Maximum Cursor Size’ property value to -1 if you want records until Siebel encounters an end of file
– Set it to ‘0’ to get 10000 records
– If you set any number greater than 0, Siebel fetches that many number of records
Same as DSMaxFetchArraySize, this property also has performance impact. So before setting this property value to -1, check all other possibilities to avoid it.
If your Siebel application is using IBM DB2, then you need to check another two parameters – DSPreFetchSize and DSMaxCursorSize.
Do you have any question? Please feel free to comment below.
Keep in touch, follow TechOneStop on Facebook / Twitter / LinkedIn / Goolge+.
<< The method IsPortableUIMode is not supported Error has occurred executing a Sql >>