"č" converted to "c" when querying through SQL Server 2008

Question

When Selecting/Inserting into my SQL Server 2008 database, Central European characters are being converted.

For example:

Select 'Kutenič'

Returns:

Kutenic

Why is this? And how can I prevent this from happening? Would my ASP.NET Web Application have a similar problem?

Answer 1

Use the N prefix

Select N'Kutenič'

Without it the string will be cast to the code page of your database's default collation.

Or set your database's default collation to one of the following if that is more appropriate for your locale.

Albanian_X
Albanian_100_X
Bosnian_Latin_100_X
Croatian_X
Croatian_100_X
Czech_X
Czech_100_X
Estonian_X
Estonian_100_X
Hungarian_X
Hungarian_100_X
Hungarian_Technical_X
Hungarian_Technical_100_X
Latvian_X
Latvian_100_X
Lithuanian_X
Lithuanian_100_X
Polish_X
Polish_100_X
Romanian_X
Romanian_100_X
Serbian_Latin_100_X
Slovak_X
Slovak_100_X
Slovenian_X
Slovenian_100_X
Turkmen_100_X
SQL_Croatian_CP1250_X
SQL_Czech_CP1250_X
SQL_Estonian_CP1257_X
SQL_Hungarian_CP1250_X
SQL_Latin1_General_CP1250_X
SQL_Latin1_General_CP1257_X
SQL_Latvian_CP1257_X
SQL_Lithuanian_CP1257_X
SQL_Polish_CP1250_X
SQL_Romanian_CP1250_X
SQL_Slovak_CP1250_X
SQL_Slovenian_CP1250_X

Answer 2

I had the same issue to solve a few years ago. I just remember that the two tracks we followed to find the solution were:

1. As proposed by #Martin, we checked the collation issue
2. but I think the right solution was to change the field data type, where char\varchar fields could not store characters from more than one character set. We had then confusing problems between french and roumanian alphabet. The solution was then to switch the char\varchar fields into nchar\nvarchar (UTF-8 compatible) fields.