Collation issue: some characters cannot be saved

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Collation issue: some characters cannot be saved

Author

Topic

pnkFloyd
Starting Member

8 Posts

Posted - 2009-04-21 : 07:00:36

Hi,

I have a problem with inserting lithuanian symbols.
Here is my table definition:

CREATE TABLE lt_test (
myfield1 varchar(20) COLLATE Lithuanian_CI_AS NULL
)

Default collation for server and this database (DB1) is SQL_Latin1_General_CP1_CI_AS.

The problem is when I insert values with lithuanian symbols (like C,S,Z with caron) these symbols are replaced with Latin equivalents (C,S,Z). For example:

INSERT lt_test VALUES ('abcd_'+'Š')
SELECT * FROM lt_test

would return 'abcd_S'.

I have no problem with another database which has default collation Lithuanian_CI_AS.
I cannot change collation for database DB1. And I also tried using char(n) for inserting lithuanian symbols like:

INSERT lt_test VALUES ('abcd_'+char(208))

In that case char(208) is replaced with question mark - 'abcd_?'.

What am I doing wrong? Is any possibility to solve this issue without changing database collation?

Thanks in advance

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-21 : 14:33:18

I'm not super familar with this type of issue, but one suggestion I would make is to use unicode datatypes: NCHAR, NVARCHAR, etc.. Also use a unicode function to cast: NCHAR(208) instead of CHAR(208).

pnkFloyd
Starting Member

8 Posts

Posted - 2009-04-22 : 04:05:45

Thank you Lamprey for your suggestion. Unicode datatypes does work but unicode functions does not in my case. The problem is I can not change all the fields I need to Unicode.

But after some experiments I finally found a solution. The SQL server's default collation MUST be Lithuanian_CI_AS ! While database can be SQL_Latin1_General_CP1_CI_AS or whatever - it does not matter. In that case if I use COLLATE Lithuanian_CI_AS when creating column it will work as expected.

So, now I have to reinstall SQL server with correct collation as default. And I think this is a bug of SQL Server 2000 (SP4 did not fix it).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 04:09:43

Works for me

DECLARE	@Sample TABLE
	(
		myfield1 varchar(20) COLLATE Lithuanian_CI_AS
	)

INSERT	@Sample
SELECT	'abcd_'+'Š' COLLATE Lithuanian_CI_AS

SELECT	*
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 04:11:38

If I run

SELECT SERVERPROPERTY('Collation')

I get "Finnish_Swedish_CI_AS" in return, which is different from lithuanian.

E 12°55'05.63"
N 56°04'39.26"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2009-04-22 : 08:19:25

The reason NCHAR(208) doesn't work is that it's the wrong value.
The Unicode code point for a capital S with caron is U+0160, so it should be NCHAR(352).

pnkFloyd
Starting Member

8 Posts

Posted - 2009-04-22 : 13:38:00

I tested it once more like Peso did:

INSERT lt_test SELECT N'LithuanianSymbols' COLLATE Lithuanian_CI_AS

and it works for me too.

But... When I SELECT it shows correct values ONLY in these situations:

1) in SQL Query Analyzer on the same server. In SQL Server Enterprise Manager it shows wrong characters. This server has default collation SQL_Latin1_General_CP1_CI_AS.

2) on other two servers it shows correct values in both SQL Query Analyzer and Enterprise Manager. Those servers have default collation Lithuanian_CI_AS.

However when I SELECT through OLEDB connection from PHP or ASP web pages it also shows wrong results. I don't know which connection use EMS SQL Manager but it also displays wrong characters. So I start to think that the problem may be is in OLEDB?

Anyway I have no problems with databases on those two servers with default collation Lithuanian_CI_AS. One database has default servers's collation and another has SQL_Latin1_General_CP1_CI_AS but has some tables with columns with collation:

INSERT myTable (LithuanianField) VALUES ('LithuanianSymbols')

and it works as expected.

pnkFloyd
Starting Member

8 Posts

Posted - 2009-04-22 : 13:51:03

You are right Arnold about char(208). I used it just for example because some browsers could display mess if I used correct one (U+0160)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2009-04-22 : 15:07:42

quote:
Originally posted by pnkFloyd

You are right Arnold about char(208). I used it just for example because some browsers could display mess if I used correct one (U+0160)

Oops, sorry! I didn't make it obvious enough: I was commenting on Lamprey's post that suggested using NCHAR(208).

The reason that CHAR(208) may or may not works is that the character it chooses depends on the (character set of the) default collation of the context it's being used in. So if that context is Lithuanian_CI_AS then CHAR(208) is S-caron, because it's using Windows CP 1257. If that context is SQL_Latin1_General_CP1_CI_AS (or indeed Finnish_Swedish_CI_AS) then CHAR(208) is taken from Window CP1252 and is capital Eth.

The reason you were getting a question mark in the last bit of your original post is (I think) that the Eth was then getting converted into a character in Window CP 1257, which doesn't have any Icelandic characters, so you get the last resort down-conversion character, which is an ASCII question mark.

pnkFloyd
Starting Member

8 Posts

Posted - 2009-04-24 : 02:27:02

quote:
Originally posted by pnkFloyd

But after some experiments I finally found a solution. The SQL server's default collation MUST be Lithuanian_CI_AS ! While database can be SQL_Latin1_General_CP1_CI_AS or whatever - it does not matter. In that case if I use COLLATE Lithuanian_CI_AS when creating column it will work as expected.

I was wrong. Now it works as expected only when database's collation is Lithuanian. And that is strange because I know it WAS working with database's Latin collation (just columns with Lithuanian collation) before server crash. I don't remember what was server's collation but I think it is not very important.

As I wrote on above posts, server CAN save lithuanian symbols. The problem now is with displaying them and with N'prefix for saving. I didn't need N'prefix before server crash.

So, now I am almost sure the problem is with OLE DB. And how OLE DB works with international strings may be depends on Regional settings of server - default System locale actually (which is English(United States) at the moment). I will try to set it Lithuanian and will see if that works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources