SBL-DAT-00500 SBL-EAI-04451 when there are 10000 or more records using Web Service based on EAI Siebel Adapter or EAI UI Adapter

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.