I am trying to implement paging to limit the data returned in an application using SAP Business One 8.82 PL05 and SQL Server 2008.
The recommended way to implement paging in SQL Server is to use the Row_Number() function to assign a sequential id to each row which can then be used in the WHERE clause to limit the rows returned to the user.
Unfortunately, it looks like the Row_Number() function throws an exception when used in either Query Manager or in an SAPbobsCOM.Recordset.
Since users already have a SQL connection through the DI-API, I would rather not have to build a separate read-only layer to access some of the more advanced features available in SQL Server.
Is it possible to use this function? or are there any other recommendations to implement paging in a SQL query within the restrictions of the SAPbobsCOM.Recordset object?