Let's see if I can explain this clearly...
There is a production order posting routine in codeunit 5407 (the exact code is not significant) that worked under C/Side but does not work after conversion to SQL. Investigation revealed that the problem is the inherent difference in sorting between the Navision 'code' datatype and and the SQL 'varchar' datatype.
The field in question is 'Operation No.' in the table 5409 Prod. Order Routing Line. In C/Side this field is type 'code'. The values that the client uses in that field are all numeric (90, 100, 560, etc.). The posting routine sets the key (which doesn't include the 'Operation No.' field) descending (ascending,false), and filters on a single production order. The records at that point are sorted numerically on Operation No. , since a C/Side 'code' field will sort numeric values numerically. The code needs to find the last Operation No., which it does, since the sort is descending, with a Find('-'). In C/Side this works fine because the dataset is sorted numerically descending by Operation No. (600,200,150,100,90,50,10).
In SQL, the 'Operation No.' field is type 'varchar,' which always sorts alphabetically. So when it gets to the Find('-') the records are not in order (90,600,50,200,150,100,10), it grabs the wrong record, and the procedure fails a few lines later.
The code in both versions is absolutey identical.
So the really puzzling thing is that the code is obviously expecting the Operation No. to be sorted numerically in the code field, so it is bound to fail, always, after conversion to SQL which forces the Operation No. to be sorted alphabetically.
Is this a widespread problem? We have never seen or heard of it before now, but changing all 'code' fields to 'varchars' seems like a very major conversion issue, with innumerable possible consequences.
Is there anything to be done??? Are there SQL settings we aren't aware of?