Hi Harm,
We’re always happy to contribute Harm. I think the frustration is we couldn’t see any light at the end of the tunnel on this one. However, perhaps there is something that can be done to help.
I took your demo sample Harm and experimented with it by adding the data where the sql server collation and string comparison differs. After cycling through the various options, I hit upon one that worked. I took this and added the code to change the locale prior to finding and this has worked.
I like Marco’s suggestion. If we had a utility that took a sql server collation and then generated the appropriate local settings for string comparison that would go a long way to taking out the guesswork.
Totally agree. So far, in DF20 we haven’t noticed any change with the embedded database collation and string comparison. My understanding is as long as we don’t try to mix databases i.e. sql server with unicode characters (nvarchar) and embedded it should be fine. However, this is not something we support in our application. There is no half-n-half, sites are entirely embedded or sql server.
Assuming my understanding is correct then the utility you mention should only be needed when mixing databases? Harm I am certainly interested to take a look even if it is just to get a better understanding of ICU collations. Thanks.