That will certainly allow all Scandinavian characters and Western European, but not Greek and Turkish too.
Also, it does not capture the more complex dictionary rules I mentioned, even for the Scandinavian languages, because SQL collations are very much lacking here.
For example, "AA" is order between "A" and "B". In the Danish dictionary, this is actually ordered after "Z" because it is equal to "Å" which is last in the alphabet. The Windows collation for Danish/Norwegian does capture this and other similar language rules.
The arguments presented there against windows collations (around extended characters converting incorrectly) are equally applicable to SQL collations, and SQL collations are inferior in representing all language rules. This is really why windows collations were introduced - to align with the OS and fully represent all language rules - for a particular locale. But they, nor SQL collations, do not unify code pages and those issues - Unicode does.
Hi Dean,
I don't understand why you say that will not work. I have had one database the last 5 years including of course western european and US languages but also include Korean, Chinese and Turkish. It works fine with the SQL collation as described but if using windows collation it gives the noted errors (refer to the other thread).
So without being SQL expert I have tested real life examples and know what works and what does not work ... I assume this is due to the different handling of codepages for SQL and Windows collation (also refer to previous thread where I refer to MS article.
I assume this database is also an OEM850 code page? Are all connecting clients using the same code page to connect or have you disabled the "Validate Code Page" check box?
I think these discussions are less about SQL and more about code page issues in general.
I don't doubt your system is working; but we need to define working and be clear what you are doing here - if you are mixing these languages then you are not using the dictionary aforded by the collation itself, but just using it as a storage repository of bytes. (In this scenario I would choose instead a binary windows collation, as I mentioned). I am saying that there is no single code page that will provide the dictionary comparisons and ordering of these three languages correctly. I have also seen an installation using Japanese (a DBCS code page) that used a binary windows collation and therefore had no ordering semantics at all - however the binary order approximately matched the grouping of the double-byte characters in Japanese, so it was a reasonable compromise. Almost any collation could be used in that case - and I expect that's also true of your example. You are not actually using the collation for anything, but if that meets your needs, its fine.
I don't believe the error your are referring to in the thread is due to a windows collation but due to a code page mismatch. C/SIDE uses OEM characters for almost everything so any files (e.g. a backup) it writes are using the OEM of the machine that client is running on (e.g. 850). If you hand that file to a machine not using 850 (e.g. 437) - you will get conversion errors during the restore. This is the case for both windows and SQL collations, and therefore I don't see this as an argument for SQL collations.
Finally, it is absolutely right that SQL collations and Windows collations are very different, as the article you refer to is stating. This is why I would always recommend a Windows collation - they capture the full semantics of the language - assuming that is important for an installation - and SQL collations do not. They are also, in terms of a single-byte system, future-proof and have dispensed with the OEM code pages altogether.
I have been asking around the last 5 years to different MS employees to find an optimal collation.
What we want is:
I my scenario I have a central database with SQL collation 850 and western european/US clients as well as special clients for Chinese, Turkish and Korean. With my standard european client I can make backups and restore of all companies into testdatabases etc. without problems.
However if I perform exactly the same with the only difference that the database has windows collation Danish/Norweigan I receive following error when performing database test of trying to make a navision backup of my Chinese, Turkish and Korean companies "There is an error in the database structure …….” I have even tried then to backup the chinese company with a chinese client but still error.
That's why I have concluded that I need to use SQL collation when having a central datastore because of the difference in the handling of non-unicode characters in Windows and SQL collations as described in the MS article.
However I am still searching for advices about optimal choise of collation (that supports most languages) for central database storing data for all countries around the world.
Hi Kim,
There is no single choice of collation for the scenario you have, as long as code pages are involved (and NAV currently is only a code page based product). This is why there are so many different collations for each locale-specific language rules. If sorting and comparison of data is not important, meaning you can tolerate inaccuracies, then the choice of collation is really arbitrary too.
The "Validate Code Page" setting was introduced to aid your kind of scenario, and is a back door - it is explicitly breaking the contract between client and server about character conversions by avoiding the code page check which drives the ANSI<-->OEM translation on the client machine.
If you use it, you are accepting possible conversion problems (or avoiding characters that have those problems, which must be difficult running Chinese, Turkish etc all in a code page of 255 characters) and also relying upon, in your case for example, the scandinavian comparison and sorting rules being applied to Chinese and so on - which is not predictable.
It is great that this setting is working for you. However, I would not generalize this into a recommendation for everyone, either to use SQL collations or to disable the "Validate Code Page" option.
I agree to that.
However for a company who understand these issues and still want to have all different countries in one database I can only recommend SQL collation as windows collations give errors.