What collation to use?

Reply

zaloo78 Posted: 11-14-2007 15:28

rated by 0 users

Hi all!

 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.

Reply

Erik P. Ernst replied on 11-15-2007 3:35

rated by 0 users

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:

• Text files exports can change special characters
• NAV databasetest or backup of company (which automatically also executes as database test) will result in error message “There is an error in the database structure …….” And NAV backup will NOT be possible

CONCLUSION:

• Collation for the SQL database for a global NAV system should be a SQL Collation e.g. “Scandinavian dictionary order, case-sensitive, for use with the 850 (Multilingual) character set.”
• NAV collation when creating database should be a SQL Collation e.g. “Scandinavian dictionary sort order, code page 850, case-sensitive, accent-sensitive (59)”
• For optimal performance make sure that the SQL collation and NAV collations are identical.
• The selected collation can be case-sensitive and accent-sensitive if wished

Which collation:

• The Scandinavia (850) collation includes all standard West-European and US characters and in addition the special Scandinavian characters.
• One known issue with using above SQL collation is that NAV will NOT distinguish between V and W (meaning that in code fields should NOT be used equal names except for V/W)
• In general when having double-byte characters or other special country characters requiring special OS in same central database these characters should be AVOIDED in all CODE fields.

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.

Reply

zaloo78 replied on 11-15-2007 11:05

rated by 0 users

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.

Reply

Kim Mohr Larsen replied on 11-17-2007 13:04

rated by 0 users

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.

Reply

Kim Mohr Larsen replied on 11-17-2007 13:09

rated by 0 users

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.