SQL Collation and Codepages

ta5

Hi

We have a strange behaviour and I shall be happy if somebody could shed some light on it...

The story is to convert a native db into sql 2008r2. After checking the date fields in native and backing up, the nav restore fails because of invalid characters in customer table (character š = alt+0154). When I uncheck the checkbox "validate codepage" the nav restore on sql works, but thats not we want to do.

The server and the client where the native backup was coming from and the destination sql server have the same code page settings. In the "change database" settings the collation is set to windows. When I set to sql collation (type 41) it also works, but as far as I understand the installation guide sql collation should not be taken.

I'm interested on some theory about that, especially why the change from windows collation to sql collation makes the difference. Other question is, whats best practice regarding these topics when planning a migration to sql?

Thanks in advance.
Thomas

kine

:-) once again, some czech database...

Again, main thing is that you need to use correct language for non-unicode applications on your computer when making/restoring the backup. In this case, if I am correct, Czech one...

ta5

Hi Kine
Thanks for replying. Well, its a czech character, but its a swiss database, but the name of the customer seems to be czech and so this character found its way to the "search name". Does it make a different whether the value is in a normal field or in an indexed field?

Anyway, I'm still unsure about the correct solution, because setting non unicode codepage to czech, maybe there might another field in the db from another character set...

Thanks in advance for your comments.
Thomas

ta5

We have set the collation now to sql sort "Western-European dictionary sort order, code page 850, case-sensitive, accent-sensitive (41)" which works ok.