It’s been discussed before (http://dynamicsuser.net/forums/t/17457.aspx etc.), and previous it has been suggested to used SQL Collation Scandinavian (59), even when Microsoft recommends to use Windows collation.
The problem is searching for the Danish double AA - which is sorted like the Danish letter Å.
Using the Windows collation Danish_Norwegian_CI_AS, then it’s sorted correctly, but when searching then no results shows up.
Is it still a general recommendation to use the SQL collation Scandinavian (59) for Danish clients, or has there been any updates on this area?
Erik, there have been no updates that I am aware of and the problem with the @ operator not working with these double characters, in a windows collation, is still present in NAV. I assume this is your problem.
The only way this works currently is to use a case and accent insensitive windows collation and avoid the use of the @ operator. Of course, I realise this is not as flexible as a case sensitive one, where you can on-the-fly enforce an insensitive search with @ as needed.
We typically use the SQL Collation Scandinavian (59) for our solutions, because of the double character problem, so this is an interesting topic!
Is it considered an error that the windows collation doesn’t work in regards to double characteres when searching with @ or is there an official explanation for this behaviour?
It is considered an error. When building the SQL LIKE value for the use of @, characaters are grouped with their case and accent insensitive counterpart characters - but it is these that do not match the double characters present in some locales. E.g., in Danish if the filter was ‘@aa’ then we would build:
LIKE ‘[aAªáÁàÀâÂäÄãÃåÅ][aAªáÁàÀâÂäÄãÃåÅ]’
Although this matches a field value of ‘AA’ with a SQL collation it does not with a windows collation.
So a different approach is needed by NAV, for the insensitive match.
The fix requires quite a different approach to the above stated LIKE grouping and as such is difficult to patch into current versions, which is why there has been pushback in fixing this as a Sustained Engineering fix. I therefore would not expect this to change until 7 where there is some investigation into the case insensitive searching, but I cannot confirm that it will change even then.
We have another problem with the å / aa search! That is in the outlook sync where contacts with aa in the name can’t be found and sync fails. Any surgestions?