Unable to find usable Collation - HELP :-(

pdj

My customer recently migrated from Native to SQL but encountered several sorting issues. We picked the Danish_Norwegian_CI_AS collation, which we also used during tests.
During migration we handled most of the sorting issues by prefixing primary key fields with zero. They had numeric Location Codes with different length and similar issues. We also changed a few records, because the code assumed numbers came after letters.
But our main problem is regarding SQL Servers attempting to be clever regarding Danish double characters. When the data includes “aa” it is considered as a single letter placed after z. This has been giving problems, because we have a code field which is build by a hierarchy of 8 independent characters. Like A02AA01A and A02AD01A. But the codes should never consider AA as a combined letter. This has caused us problems in sorting and searching for records and several statistics based on this hierarchy. We considered changing the value of the field by inserting a space between each character and handle it everywhere. But the field-type is used a lot of places and this task would be very big. Instead we decided to change the collation of the column directly in SQL. However; even though this seems to work fine, we risk it might be changed back by accident in the future when one of the tables are updated. We also fear we might forget this special requirement when we add a new field in some table with this kind of contents. Instead we ended up trying to change the collation for the entire database. We can easily live with “aa” appearing early in sorting – the never complained about this while running Native DB.
That was a long story, but now I need to know which “clean” collation to pick, that doesn’t have any special handling of different combinations of characters. On my own Danish XP box it only shows me 6 different Windows collations, and none of them are pure English nor Latin. Several other collations appear if I clear the “Validate Code Page” checkmark. But still none of them seem pure English or Latin. It seems I need to pick a SQL Collation find a simple Collation. I’m currently testing with the “Western-European dictionary sort order, code page 1252, case-insensitive, accent-sensitive (52)” collation. But all documentation recommends using a Windows collation, and SQL Collation are only kept for backwards compatibility.
Is there a Windows collation this works as I require, or do I need to use a SQL collation? Is there any problems using a SQL collation? We are currently using NAV5SP1 and SQL2005, but someday we’ll be using the RTC and SQL2008 or later – should that cause any concern?

Thanks in advance for any input regarding the subject.

Edit: Changed the subject...

pdj

Bump?

stryk

Hi Peter,

a little while ago I had similar trouble when migrating from native NAV 3.60 to SQL (2008) and NAV 2009. We ended up in using a "SQL Collation" (in our case 41 if I remember right) instead of a Windows collation - since then it's all working OK ...

Regards,
Jörg