SBL-DAT-00500: There were more rows than could be returned. Please refine your query to bring back fewer rows

Applies to:

Siebel CRM - Version: 8.0 [20405] to 8.0 [20405] - Release: V8 to V8
Siebel CRM - Version: 8.0 [20405] to 8.0 [20405]   [Release: V8 to V8]
Information in this document applies to any platform.
Product Release: V8 (Enterprise)
Version: 8.0 [20405]
Database: Oracle 10.2.0.2
Application Server OS: Microsoft Windows 2003 Server SP2
Database Server OS: Microsoft Windows 2003 Server SP2

This document was previously published as Siebel SR 38-3357468797.

***Checked for relevance on 01-Sep-2010***

Symptoms

While querying List of values for COUNTRY typed LOVS, not all records are getting retrieved.

In the spool file, it showed that the following query Retrieved 52 records.:

SELECT statement with ID: C2CA278
SELECT /*+ ALL_ROWS */
T1.CONFLICT_ID,
T1.LAST_UPD,
T1.CREATED,
T1.LAST_UPD_BY,
T1.CREATED_BY,
T1.MODIFICATION_NUM,
T1.ROW_ID,
T1.NAME,
T1.ORDER_BY,
T1.RPLCTN_LVL_CD,
T1.SUB_TYPE,
T1.TYPE,
T1.VAL,
T1.CODE
FROM
SIEBEL.S_LST_OF_VAL T1
WHERE
((T1.ACTIVE_FLG = :1 OR T1.ACTIVE_FLG IS NULL) AND
(T1.CODE != 'CLASS' OR T1.CODE IS NULL) AND
(T1.TYPE = :2 AND T1.BU_ID IS NULL))
ORDER BY
T1.ORDER_BY

Bind variable 1: Y
Bind variable 2: COUNTRY

The same statement, executed in Toad, retrieves 240 records.

Also, scrolling through the View 'List of Values View' by filtering for TYPE = COUNTRY the following error message appears:

SBL-DAT-00500 - 'There were more rows than could be returned. Please refine your query to bring back fewer rows' Please continue or ask your systems administrator to check your application configuration if the problems persists. (SBL-DBC-00105)

ORA-24345: A Truncation or null fetch error occurred.

Cause

This issue occurs if during the query statement you enter a VAL with 30 Characters for a LOV record.

The errors: SBL-DAT-00500 and ORA-24345 happen due to the Oracle Bug 5746532.

The Oracle Bug 5746532 refers to the Oracle client version 10.2.0.2 "Only" (The supported and Suggested version for Siebel 8.0).

Solution

Solution: Upgrade to Oracle DB 10.2.0.3 or above.

Note: The Oracle 10.2.0.3 it's a part of Oracle 10g R2. As all Oracle 10g R2 are supported, upgrading the Oracle DB version to 10.2.0.3 will make your environment to continue in a supported platform (Oracle 10g R2) a well.

References

Applies to:

Siebel CRM - Version: 8.0 [20405] and later   [Release: V8 and later ]
Siebel CRM - Version: 8.0 [20405] and later    [Release: V8 and later]
Information in this document applies to any platform.

Symptoms

Whenever a service based on EAI Siebel Adapter or EAI UI Data Adapter business service run to insert a child record when there are already 10000 records associated to the parent, i.e. Contacts for an Accout, Usert to a Responsibility, an error message is received as the following:

"Method 'NextRecord' of business component 'User' (integration component 'User') for record with search specification '[Login Name] = "FIRST.LAST@COMP.COM"' returned the following error:"There were more rows than could be returned. Please refine your query to bring back fewer rows(SBL-DAT-00500)"(SBL-EAI-04451)"

"Method 'NextRecord' of business component 'Contact' (integration component 'Related Contact') for record with search specification '[Middle Name] = "Cont_MN" AND [Last Name] = "Cont_LN" AND [First Name] = "Cont_FN"' returned the following error:"There were more rows than could be returned. Please refine your query to bring back fewer rows(SBL-DAT-00500)"(SBL-EAI-04451)"

Cause

This behavior is expected as the default 'ForwardBackward' execution mode is used to limit fetched records from the Select statement needed in this kind of operations (as explained in Document 478896.1 – 'SBL-DAT-00500: There were more rows than could be returned. Please refine your query to bring back fewer rows').

In the context of EAI Siebel Adapter and EAI UI Data Adapter business service, the 'ExecutionMode' is a hidden input argument for Insert methods, and included in Bookshelf > Integration Platform Technolgies: Siebel Enterprise Application Integration > EAI Siebel Adpter Business Service > EAI Siebel Adapter Business Service Method Arguments, Table 23 and Table 24 with the following description:

"Used to set the direction of a query on a business component. Valid values are ForwardOnly and Bidirectional. The default is Bidirectional.

ForwardOnly is more efficient than Bidirectional, and should be used in cases where you need to process a large number of records in the forward direction only (such as for report generation).

For more information on executing queries, see the topic on the ExecuteQuery business component method in Siebel Object Interfaces Reference"

As opposite to 'ForwardBackward' value, 'ForwardOnly' does not restrict the number of fetched records during the Select statement.

Solution

In order to implement the 'ForwardOnly' execution Mode in EAI Siebel Adapter or EAI UI Data Adapter services, the argument 'ExecutionMode' has to be added in the method's arguments.

For instance, using 'Siebel Account' Business Service (based on EAI Siebel Adapter), InsertOrUpdate method to link Contacts to an Account:

a. From Siebel Tools, Business Services, select 'Siebel Account' and expand it.
b. From Business Service Method, select InsertOrUpdate.
c. Select Business Service Method Arg.
d. Add a new Argument with name 'ExecutionMode'.

If needed, publish again your service (Web Service, JCA Code Generated).

When invoking the service, set the value 'ForwardOnly' to argument 'ExecutionMode'.

Repeat the required call. This will enable the option not to limit fetched records and allow the Insert operation for the child record.

The CR # 12-1VR2Q5J was logged to improve the current information about this parameter in Bookshelf, so it will explicit indicate its use while fetching records in this type of solutions.

Note

The following situation was observed using this Mode.

Using ExecutionMode=ForwardOnly there was an issue trying to change the primary of a MVG ex. IsPrimaryMVG="Y" on a Position of an Account.

When siebel tried to update the parent record (Account) it failed because the cursor is in ForwardOnly mode. So it could not get back to the parent record to update the primary position returning the error:

"An end of file error has occurred. Please continue or ask your systems administrator to check your application configuration if the problem persists".

So, when including MVG, this mode should not be used.

References

BUG:12-1VR2Q5J - ISSUE WITH WEB SERVICE WHEN THERE ARE MORE THAN 10K RECORDS
BUG:12-IY19E5 - DSMAXFETCHARRAYSIZE SHOULD BE A COMPONENT PARAMETER RATHER THAN AN ENTERPRISE PARAMETER
NOTE:477558.1 - Setting the Parameter DSMaxFetchArraySize to -1 Could Cause Problems with Siebel Object Managers in Siebel 7
NOTE:478896.1 - SBL-DAT-00500: There were more rows than could be returned. Please refine your query to bring back fewer rows
NOTE:524329.1 - SBL-DAT-00500: "There were more rows than could be returned" error received when running a BS Method

Applies to:

Siebel Tools - Version: 7.5.3.3 SIA [16172] - Release: V7
Sun Solaris SPARC (64-bit)
Product Release: V7 (Enterprise)
Version: 7.5.3.3 [16172] Com/Med
Database: Oracle 9i
Application Server OS: Sun Solaris 8
Database Server OS: Sun Solaris 8

This document was previously published as Siebel SR 38-1357606671.
Checked for Relevance on 23-Feb-2010

Symptoms

We are receving the following error when running a Business Service Method from a Repeating Component Request in Production:

SBL-DAT-00500: "There were more rows than could be returned. Please refine your query to bring back fewer rows."

I have checked SupportWeb and have found similar postings (e.g. ALERT 951, however, this was due to other customers using the ForwardBackward ExecuteQuery method.

In this case, we are using the ForwardOnly ExecuteQuery method, which should not even utilise the DSMaxFetchArraySize parameter and therefore should not limit the number of records retrieved. In our case for the RCR, we are trying to fetch over 200,000 records to process by the Business Service method.

Please can you investigate why we are still receiving this error using the ForwardOnly mode of ExecuteQuery. The eScript method is attached to the SR.

Thanks

Phil Schofield

Solution

Message 1

For the benefice of other readers,

DSMaxFetchArraySize parameter is designed for limiting the number of records fetched in a query, its aim is to avoid performance and memory management issues.

As explained in the Alert 951, it is involved only when queries are performed in a Forwardbackward mode, but the error message has been experienced even setting the query mode to ForwardOnly; the change request number 12-MRLIS5 (SBL-DAT-00500 error occurs even when querying with ForwardOnly query mode) has been raised to address this unexpected behavior.

If a query must fetch more than the number of records set in this parameter, several solutions are available:
1) Set the DSMaxFetchArraySize to -1 in order to not restrict the number of records fetched
As explained in the Alert 951, this solution must be limited only to dedicated areas and not extended to the entire enterprise. Furthermore, it must be thoroughly tested.
This can be for example implemented setting the MaxFetchArraySize to -1 in a dedicated cfg that is used only by one Object manager; all other OMs use the standard value.

[...]

Message 2

[...]

2) Split the query
This solution offers to split the query into several smaller, so that the error message will not occur.

For this the DSMaxFetchArraySize must be set to 0 (10 000 by default).
The query must use the ForwardBackward query mode in order to involve the above parameter.

When performing the query it is necessary to add a search criterion in order to fetch the records that follow the one already processed.
This is possible sorting the query using, for example the [Id] field and storing the last Id processed. In the next query, this information may be used for getting the following records.

Example of implementation of this solution (with the DSMaxFetchArraySize = 0):
    var boSR = TheApplication().GetBusObject("Service Request");
    var bcSR = boSR.GetBusComp("Service Request");

    var lastIdProcessed = "0";
    var endOfFetch = false;

    while(!endOfFetch)
    {
        bcSR.ClearToQuery();
        bcSR.SetViewMode(AllView);
        bcSR.SetSearchExpr(" [Id] > '" + lastIdProcessed + "'");
        bcSR.SetSortSpec("[Id] (ASCENDING)");
        bcSR.ExecuteQuery(ForwardBackward);
        var isRecord = bcSR.FirstRecord();
        if(!isRecord)
            endOfFetch = true;

        while(isRecord)
        {
            try
            {
                // Perform the process here
            }
            catch(e)
            {
            }    
            finally
            {
                lastIdProcessed = bcSR.GetFieldValue("Id");
                isRecord = bcSR.NextRecord();    
            }
        }
    }
    bcSR = null;
    boSR = null;

[...]

Message 3

[...]

Other solutions are available such as using EIM for processing this huge amount of records. The customer has finally chosen this solution.

Thank you,

Applies to:

Siebel CRM - Version: 7.8.2 [19213] and later   [Release: V7 and later ]
Information in this document applies to any platform.

Symptoms

When export a large product model using Workspace, users received the following error message:

There were more rows than could be returned. Please refine your query to bring back fewer rows(SBL-DAT-00500)"(SBL-EAI-04376)
 

Cause

The cause of the issue is due to a Query or Search is performed by users which is trying to return more rows than is allowed in a result set.  Parameter DSMaxFetchArraySize is set to a low value.

Solution

To resolve the issue, customer sets the value for parameter DSMaxFetchArraySize to 10,000 or -1.

In version 7.8x, parameter DSMaxFetchArraySize is an advanced parameter and it is not visible in GUI by default. It can be set using command line server manager utility as follows:

Srvrmgr> change parameter DSMaxFetchArraySize=-1 for named subsystem ServerDataSrc
Srvrmgr> list advanced params dsmaxfetcharraysize for named subsystem serverdatasrc

In order to take the above settings effect, restart all the Siebel servers.

References

NOTE:493107.1 - Cannot access all assets in view
NOTE:478896.1 - SBL-DAT-00500: There were more rows than could be returned. Please refine your query to bring back fewer rows

Applies to:

Siebel Tools - Version: 7.5.3.5 SIA [16183] and later   [Release: V7 and later ]
IBM AIX on POWER Systems (64-bit)
Product Release: V7 (Enterprise)
Version: 7.5.3.5 [16183]
Database: IBM DB2 8.1 FixPack 3
Application Server OS: IBM AIX 5L 5.1
Database Server OS: IBM AIX 5L 5.1

This document was previously published as Siebel SR 38-1558305681.

Symptoms

We have a escript written on campaign Buscomp where it retrieves a record from campaign list contact and execute custom logic to generate uniquie code for every record for every contact. The code gets executed successfuly for first 10k records and for 100001 records it error out saying

SBL-DAT-00500: "There were more rows than could be returned. Please refine your query to bring back fewer rows."

There is a siebel alert 951 for this problem but siebel doesn't suggest to change the DSMaxFetchArraySize to -1

We cannot refine the query to return less then 10K records.
There is a high impact on the business because of this issue.. We have noticed this error in the GUI which has more then 10K records. The script written on Campaign BC is follows and there is no way we can refine the query to limit less records ...


    if ((FieldName == "Generate Priority Code")&& (FieldValue == "Y"))
    {
        var bcCampCon = this.BusObject().GetBusComp("Campaign List Contact");
       
        bcCampCon.ClearToQuery();
        bcCampCon.SetViewMode(AllView);
        bcCampCon.ActivateField("Source Code");
    //    bcCampCon.SetSearchExpr("([Source Code] IS NULL)"); //defect 20804 , r7.5,PD --start/end
        bcCampCon.ExecuteQuery();
        var isRecord = bcCampCon.FirstRecord();
          
        while(isRecord)
        {
            SourceCode = bcCampCon.GetFieldValue("Source Code");//defect 20804 , r7.5,PD --start
            if(SourceCode.length !=8)
                  {     //defect 20804 , r7.5,PD --end     
                    ContactId = bcCampCon.GetFieldValue("Id");
                    svc = TheApplication().GetService("IBM DBM Priority Code");
                    Inputs = TheApplication().NewPropertySet();
                    Outputs = TheApplication().NewPropertySet();
                    Inputs.SetProperty("CampConId",ContactId);
                    svc.InvokeMethod("GeneratePriorityCode",Inputs,Outputs);
                 }    //defect 20804 , r7.5,PD --start/end
                        
            isRecord = bcCampCon.NextRecord();                  
        }
              
        bcCampCon.Clea...

Cause

Customer has written escript on campaign Buscomp where it retrieves a record from campaign list contact and execute custom logic to generate unique code for every record for every contact. 

Customer has noticed this error below in the UI which has more then 10K records. The eScript written on Campaign BC is below and there is no way customer can refine the query to limit less records due to custom script and following error is displayed

SBL-DAT-00500: "There were more rows than could be returned. Please refine your query to bring back fewer rows."
Here is sample script

    bcCampCon.ClearToQuery(); 
        bcCampCon.SetViewMode(AllView); 
        bcCampCon.ActivateField("Source Code"); 
    //    bcCampCon.SetSearchExpr("([Source Code] IS NULL)"); 
        bcCampCon.ExecuteQuery(); 
        var isRecord = bcCampCon.FirstRecord(); 
           
        while(isRecord) 
        { 
        while(isRecord) 
        {

SBL-DAT-00500: "There were more rows than could be returned. Please refine your query to bring back fewer rows."

Customer was seeing expected behavior; Execute query results set will be limited to 10K because DSMaxFetchArraysize is set to "0"as per Alert 951: Setting the parameter DSMaxFetchArraySize to -1 could cause problems with Siebel Object Managers in Siebel 7

Solution

On SQL serverDB : BusComp.ExecuteQuery2 method offers the ignore MaxCursorSize parameter that is not available in the standard BusComp.ExecuteQuery method. This parameter allows not taking care of the MaxCursorSize property defined at the business component’s level 

On DB2 customer was suggested to use BusComp.ExecuteQuery(ForwardOnly) instead of BusComp.ExecuteQuery method ,once these changes where implemented in the custom eScript ,every thing was working as expected and more than 10K records where retrieved

Change Request 12-MJTWZL has already been logged to address this enhancement request. It is targeted for v8.0 with substatus Not Started.
Siebel Technical Support

Keywords : ExecuteQuery(ForwardOnly),SBL-DAT-00500,DSMaxFetchArraySize

Applies to:

Siebel Tools - Version: 7.7.1 [18306] and later   [Release: V7 and later ]
z*OBSOLETE: Microsoft Windows Server 2003
Product Release: V7 (Enterprise)
Version: 7.7.2.1 [18353]
Database: Oracle 9.2.0.8
Application Server OS: Microsoft Windows 2003 Server SP1
Database Server OS: Microsoft Windows 2003 Server SP1

This document was previously published as Siebel SR 38-1796748021.

Symptoms

SBL-DAT-00500

Hi,

I am currently working on an upgrade from Siebel 6 to Siebel 7.7 and have an issue as follows:

I have hit an error as follows:

There were more rows than could be returned. Please refine your query to bring back fewer rows(SBL-DAT-00500)

The Business Component in question is a custom BC based on the Vanilla BC Asset Mgmt - Asset with no Search Specification.

I have built an All View based on this BC to allow users to perform queries.

I am currently working on the Dedicated Web Client only. When I visit the All View I am receiving the error above.

I have tried the following amendments to try and resolve the error but none have worked:

1. At the BC level set Maximum Cursor Size to -1
2. At the BC level set Maximum Cursor Size to 200000
3. In my Dedicated Web Client CFG file I have set the MaxCursorSize setting to -1
4. In my Dedicated Web Client CFG file I have set the MaxCursorSize setting to 200000

There are currently 120000 records in the underlying database table S_ASSET.

We have over 150000 records in S_EVT_ACT and our All Activities view works fine.

I have checked the configuration and cannot identify the issue. This BC worked fine when using Siebel 6.

Kind Regards

Cause

n/a

Solution

Message 1

For the benefit of other users:

The root cause of this behavior was not known, however the customer resolved the issue as follows:

"We re-created the Custom BC by copying the original BC Asset Mgmt - Asset and then re-applied any custom columns.

We then re-created the List Applet by copying the original applet Asset Mgmt - Asset List Applet and then re-applying any customizations."



Keywords; Upgrade, BC, asset, error, SBL-DAT-00500

Applies to:

Siebel Finance CRM Sales, SPE - Version: 7.8.2.1 [19216] and later   [Release: V7 and later ]
Information in this document applies to any platform.

Symptoms

Only in a FRA / French application

In the view Administration - Communication > All Templates, we encounter the following error message in a pop-up window:
 
SBL-DAT-00500: There were more rows than could be returned. Please refine your query to bring back fewer rows.

The log file also has an

ObjMgrLog Error 1 

SBL-DBC-00104: Erreur lors de la récupération de l'enregistrement suivant depuis la base de données.

and

SQLParseAndExecute Execute 5

ORA-24345: A Truncation or null fetch error occurred

errors directly before

Cause

There is a FRA seed template record that has a 183 character
Subject Text

while the length specified for the corresponding
Field:
Subject Text
in the Business Component:
Comm Package

has a
Text Length: of (only)
150
Type:
DTYPE_TEXT

Which can be confirmed with SQL like 

SIA782.S_DMND_CRTN_PRG where length(MSG_SUBJ_TEXT) > 150;

ROW_ID                                        LENGTH(MSG_SUBJ_TEXT)
--------------------------------------------- ---------------------
0LFRA-6J6UF@                                                    183


For CR 12-KDZMO5

Someone added

Business Component
Comm Package

Field
Subject Text

Field User Prop
Name=
Text Length Override

Value=
TRUE

which causes the limit to be enforced

Solution

Change Request
12-1Z9S1KH
Shorten S_DMND_CRTN_PRG.MSG_SUBJ_TEXT to 150 chars or less in FRA seed record

was logged to have the FRA seed record fixed

This can NOT be done through the GUI,
as even a query for the offending record with
[Id]='0LFRA-6J6UF@'

will fail - this time with the following error in popup and log file:

ObjMgrLog Error 1 0 2010-08-04 20:04:31 (oracon.cpp (3077)) SBL-DBC-00104: SBL-DBC-00104: Erreur lors de la récupération de l'enregistrement suivant depuis la base de données.

SQLParseAndExecute Execute 5 0 2010-08-04 20:04:31 ORA-24345: A Truncation or null fetch error occurred

It is possible to correct the record directly at database level, with direct SQL like

update sia782.S_DMND_CRTN_PRG set MSG_SUBJ_TEXT = substr(MSG_SUBJ_TEXT,1,150) where ROW_ID='0LFRA-6J6UF@';

Please invoke your DBA to assist with this update if needed; running the command with a different where clause might corrupt your other message template records, so if in any doubt, please create a full database backup that you can revert to if anything goes wrong before attempting this.

You'll need to commit the record change and you may need to login again or even restart the object manager, as the data could be cached.

If you have mobile clients, please query for the template with
[Id]='0LFRA-6J6UF@'
and change the "Subject Text" field - removing or changing a single character and save it
to have the change routed to your mobile clients.

PLEASE DO NOT MODIFY THIS DOCUMENT !!!

and if possible don't ask for modifications - it takes A LOT of manual changes to bring the non-ASCII characters in, and whenever it is saved again, the error message will display as

SBL-DBC-00104: Erreur lors de la r�cup�ration de l'enregistrement suivant depuis la base de donn�es.

Internal bug 9820361 is logged to address this knowledge display issue.

References

BUG:12-KDZMO5 - UNABLE TO INPUT > 150 CHARS IN SUBJECT FIELD OF SEND COMMUNICATION APPLET.
NOTE:475683.1 - How to generate tkprof/explain plan output?
NOTE:476591.1 - How To Troubleshoot the error ORA-24345 "A Truncation or null fetch error occurred"
NOTE:476760.1 - How can users trace a Siebel server task with SQL Trace and TKPROF on Oracle?
NOTE:476988.1 - WE8ISO8859P1, WEISO8859P15 and other Western code pages de-supported with Siebel 7.5 release, must upgrade to WE8MSWIN1252
NOTE:477558.1 - Setting the Parameter DSMaxFetchArraySize to -1 Could Cause Problems with Siebel Object Managers in Siebel 7
NOTE:478028.1 - Oracle CBO and Siebel Business Applications