Storing regional (slovenian) characters in the database

Question

Hello

This might be painfully obvious to some (most?) of you, however it has been bugging me for a while now.

I have two databases running on the same SQL server (2005). As far as I can see they both have the same language/regional properties. Both have collation set to "Sloveninan_CL_AS" and yet one stores all Slovenian special characters (č, ž, š) without a problem, and the other coverts them to their non-regional sensitive "matches" (c, z, s).

All strings subjected to regional characters in both databases are stored in fields of the same type (varchar).

I am wondering what other settings are there that could affect this behaviour? What additional steps can I take to ensure special characters will be saved correctly in the second database?

EDIT: The only additional information that could prove relevant I can think of is that the second ("malfunctioning") database was initially created with a different collation setting and was changed at a later time, whereas the first was (probably) created with the setting set to the current value. However I think, since the setting can be changed, this shouldn't be a problem. Also, the server has been restarted since the collation setting was changed.

Answer 1

I prefer to use the NVARCHAR() datatype instead. NVARCHAR uses Unicode, which is a lot friendlier when it comes to localization.

Anyway, it's definitely a material issue that the database was initially created with a different collation. When you set the collation on a database, what you are actually doing is setting the default collation for newly created objects. Take a look at the tables themselves. I'm willing to bet that they are still set to the old collation. You may have to recreate or ALTER the tables and indices in order for the new collation to take effect.

http://msdn.microsoft.com/en-us/library/ms175835.aspx

Answer 2

Have you definitely changed the collation on the database itself? Not just the column? When I try the following script on a test database and switch the database collation back and forth between slovenian and latin I get different results for the č character (the N prefixed version always works)

SET NOCOUNT ON

DECLARE @testtable TABLE
(
A VARCHAR(5) COLLATE Slovenian_CI_AS,
B  VARCHAR(5) COLLATE Slovenian_CI_AI
)

INSERT INTO @testtable
VALUES ('čžš','čžš')

INSERT INTO @testtable
VALUES (N'čžš',N'čžš')

SELECT *,CAST(A AS VARBINARY(6)) ,CAST(B AS VARBINARY(6))  
FROM @testtable

Slovenian_CI_AS

A     B                    
----- ----- -------------- --------------
čžš   čžš   0xE89E9A       0xE89E9A
čžš   čžš   0xE89E9A       0xE89E9A

Latin1_General_CI_AS

A     B                    
----- ----- -------------- --------------
cžš   cžš   0x639E9A       0x639E9A
čžš   čžš   0xE89E9A       0xE89E9A