Solved

Collation problem in SQL Database

Posted on 2008-10-17
9
1,263 Views
Last Modified: 2012-05-05
Our Web Product is running only for English language but now we need to run it for other language as well. Right now we want to run our product for Russian language but our database collation is "SQL_Latin1_General_CP1_CI_AS" but collation is different of client sql server. Now let me know best solution to solve it. Tell me which is the best solution for me to run my application to all languages either i need to change database collation or go for other solution
0
Comment
Question by:imrancs
  • 5
  • 2
  • 2
9 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22739489
Need to make sure all your datatypes ar unicode - e.g. instead of varchar, use Nvarchar... There is some good documentation in Books On Line - see : "International Considerations"
0
 
LVL 23

Expert Comment

by:adathelad
ID: 22739973
If the collation is different, your options are:

1) Change the collation of one of the databases to match the other. This isn't quite as simple as it sounds, as you can specify the collation at COLUMN level. So changing the collation of an existing database will only take effect for NEW objects created from then on - all existing objects will still use the original collation. So you'd really have to change the collation of the database AND all the varchar/nvarchar etc columns in the database.

2) Change your queries to include the COLLATE statement
e.g.

SELECT *
FROM DatabaseA.dbo.SomeTableA a
    JOIN DatabaseB.dbo.SomeTableB b ON a.SomeKey = b.strKey COLLATE SQL_Latin1_General_CP1_CI_AS
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22740389
Yes, that is true for collation, but in multi-lingual user world, it is not gong to translate languages...that is quite different.

Think it is a matter of having unicode character data types and a consistant collation for the database. Collations do not really apply to unicode columns (or numerics, or dates etc), just really to single byte character columns (char, varchar)

Possibly best represented by :

select * from sys.syslanguages

@adathelad : it sound like what you are indicating, is to get the content to read the same by using collation, where as collation represents a character set or code page, not a language.

e.g.

select days from sys.syslanguages where langid = 21
select days COLLATE SQL_Latin1_General_CP1_CI_AS from sys.syslanguages where langid = 21
select days COLLATE Ukrainian_CI_AI_KS_WS from sys.syslanguages where langid = 21

still all reads like russian to me...
but then as character columns, check out what happens...

select convert(varchar,days) from sys.syslanguages where langid = 21
select convert(varchar, days COLLATE Ukrainian_CI_AI_KS_WS) from sys.syslanguages where langid = 21


So, morale of the story, is collation is a code page or character set needed for character based datatypes, not unicode, and certainly not a language translation.

Language converters are not great, and needed for a true "localisation" of data from the central database to an individual user, if and only if you can identify the language of original for that data set, and the language of the recipient client...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:adathelad
ID: 22740447
Although I read the original question, I think I "read" it completely differently and took the wrong meaning from it based on similar issues I've worked on before.

The scenario I was talking about was quite rightly as you say not to do with language support, but specifically for the collation. i.e. the scenario where you have a query that is failing due to collation differences between the objects it is querying and so the need to resolve that error. I (wrongly/hurriedly) assumed that was the issue....but it's now clear I completely misread the question!
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22740475
either that or I did :0)
0
 
LVL 10

Author Comment

by:imrancs
ID: 22766414
If i change my data type to unicode then i don't need to change database collation. I also using temp tables.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22766784
If that s a question, then yes that is correct. There is one small implication, that collation can determine sort sequences as well, so might need to look into that - but in a multi-language database, the best way to display sorted data is once you know the client language, and can use collation when extracting data for a specific language that would sort data differently - collate key word is available as part of the select statement.
0
 
LVL 10

Author Comment

by:imrancs
ID: 22783629
I can not use collation in my select queries and with creating temp table because i have very huge database then what do you think? i need to change my datatype into UniCode and also need to match my database collation with client machine as well.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 50 total points
ID: 22784496
definitely unicode... no question about that.

The thing about unicode and collation is in using collation more for sort sequences than the characters themselves.

e.g.

declare @tbl table (nc nvarchar(100))

insert @tbl
select 'choir' union
select 'chess' union
select 'coin' union
select 'cat' union
select 'clown'

select nc from @tbl order by nc COLLATE Cyrillic_General_CI_AS
select nc from @tbl order by nc COLLATE Traditional_Spanish_CI_AS


So, if you truly have an internation requirement, then there are somethings you do need to know about your end user. It does get even more interesting with languages in terms of date format, number formats etc...

But, for your question, Unicode is the answer.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL: "HAVING CASE" Clause 1 26
Inserting oldest record into new table. 5 25
SSRS Enable Remote Errors 4 26
PROPERCASE SCRIPT IN SQL 3 7
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question