Diacritical Marks Disappear - SQL Server Forums

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

SQL Server Development (2000)

Diacritical Marks Disappear

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-24 : 20:21:42

Using Enterprise manager to update a column, or by manually pasting a letter, diacritical marks (letters with accent marks over them (ë), disappear when the column is updated, leaving the letter without the accent mark.

That is crazy! What gives? Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-24 : 20:29:47

This is probably a limitation in Enterprise Manager. If you paste it into another utility such as Microsoft Word, are you able to see the diacritical marks?

Why are you using Enterprise Manager to update data anyway? All data modification should be done in Query Analyzer. I realize that EM is very easy to use and is sometimes faster than writing out the query, but EM has lots of limitations and also consumes a lot of resources which is not what you want in production.

Tara

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-24 : 20:38:29

I copied the name with letters having diacritical marks from Word. I've also now tried it in Query Analyzer also.

I'm using SQL 2000.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-24 : 20:45:30

Did the marks appear in Query Analyzer?

Tara

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-24 : 20:53:10

Yes.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-24 : 20:57:12

I doubt that Microsoft has any plans to fix this since Query Analyzer is the tool to use for data modifications. So I guess you're stuck with using Query Analyzer when these types of marks appear in your data. Let us know if you need help using Query Analyzer.

Tara

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-24 : 21:00:25

I'm sorry, I misunderstood you. Yes the accented character appeard in Query Analyzer before the update is run.

Update Mailinglist_OM set lastname='Ariël' where custnum=50011000

However it did not appear in the select used to check it:
Select lastname from MailingList_om where custnum=50011000

Ariel

It's bizarre. Surely SQL2000 can deal with an extended character set.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-24 : 21:05:19

You might want to have a look at collations in Books Online (collations, selecting a collection). You probably are using one that doesn't support these marks. You can change the collation on columns or you can change the default collation on the database.

Tara

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-24 : 21:18:50

Collation may be the answer. However, I don't have control over the shared SQL server we're on. How can I confirma the collation used when the database was set up?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-03-25 : 04:32:22

The default collation for the server and table can be found with

SELECT SERVERPROPERTY('Collation')
SELECT DATABASEPROPERTYEX('your_db_name_here','Collation')
respectively.

You should be able to see the collation and character set for the column in question by using INFORMATION_SCHEMA.COLUMNS:

SELECT CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name_here'
AND COLUMN_NAME = 'your_column_name_here'

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-25 : 16:54:51

Ok, The collation is SQL_Latin1_General_CP1_CI_AS.

Does this mean no characters with diacritical marks may be inserted or updated using this collation?

If so, which collation scheme should be chosen? Also, what are the pitfalls for allowing diacritical marks in SQL 2000?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-25 : 17:00:28

I believe that the collation that you are using will support diacritical marks, but you have to use certain data types in order to get those marks in the table:

From SQL Server Books Online:
"The easiest way to manage character data in international databases is to always use the Unicode nchar, nvarchar, and ntext data types in place of their non-Unicode equivalents (char, varchar, and text). If all the applications that work with international databases also use Unicode variables instead of non-Unicode variables, character translations do not have to be performed anywhere in the system. All clients will see exactly the same characters in data as all other clients."

So alter your columns that are char, varchar, and text to nchar, nvarchar, and ntext (respectively).

Tara

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-25 : 17:06:58

Great! Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources