Im in the progress to create a new database for a company that is located all around europe (Sweden, Denmark, Norway, France, Netherland, Belgium and Austria) does anyone know what collation I should use in order to be able to enter the correct characters in Navision? They all will share one database and would be able in theory to look at each others data.
We had the same question as we have a setup much like yours. Our only additional requirement is that we need to be able to support double byte character sets also. Here are there recommendations we received by our consultant:
As promised hereby the conclusions based on my test of windows collations and sql collation on central databases with data input using different OS (e.g. double-byte)
Recommended collation today by Microsoft is Windows collation because this is never than SQL collation
However using Windows collation together with non-unicode programs is resulting in problems.
This is probably due to the handling of non-unicode in windows collations which are handled in the same way as Unicode characters where using a SQL collation, SQL Server defines different comparison semantics for non-Unicode data. For more info refer to Microsoft: http://support.microsoft.com/kb/322112
Using a windows collation on an SQL server where non-unicode data is stored and input using different OS will result in following errors:
I hope you can use this, otherwise the consultant (Kim M. Larsen) is also a member here, and might be able to answer additional questions you might have.
Tack ska du ha Erik :) (Thank you Erik)
If I have understand this correct we should use SQL Collation on the new database, the thing is that today on the Navtive databases we have been using CodePage 437 from the start 6 years ago. Should I use SQL_Latin1_General_CP437_CS_AS. My thought here is that it is compatible with the codes they have entered earlier. Perhaps we can use this when we migrate and change it later? In my opinion we should use Codepage 850 instead of 437 in the future.
In your list of countries you don't have any double-byte or "special" language not covered by the normal "Western Europe and United States" countries (regional settings). If you don't expect to include such countries the recommendation would be Windows collation Danish_Norweighan. Then you avoid the issue with V and W. However you will need to change if you later need to include languages like Chinese, Turkish etc. from another regional settings area.
PS. I am the consultant that wrote the recommendation Erik posted above, so there should be no conflicts in this answers.
I don't have specific expirience with collations using NAV db, however I would recommend that you implement the collation on SQL that you expect to use (do NOT plan to change later as this is not recommended).
You should always do a test restore to make sure you don't have any issues with special characters in the different countries and you might need to make sure the backup from the NAV db is done by the same client (or with similar regional settings) as the restore to the SQL db.