SQL Server Collation / ADO.NET DataTable.Locale with different languages
We have a WinForms application that stores data in SQL Server (2000, we are working on porting it in 2008) via ADO.NET (1.1, working with a port to 4.0). Everything works fine if I read the data that was previously written in Western European language (for example: "test", "test ù"), but now we should be able to mix Western and non-Western alphabets (for example: "test - ۓےۑ" is just random Arabic characters).
On the SQL Server side, the database was set using collation Latin1_General
, the field is nvarchar(80)
. If I run a SQL SELECT statement (eg " SELECT * FROM MyTable WHERE field = 'test - ۓےۑ'
", ignore the "*" or actual names) from Query Analyzer, I get no results; the same happens if I pass a Sql statement to the ADA.NET DataAdapter to populate the DataTable. I'm guessing it has something to do with sorting, but I don't know how to fix it: do I need to change to sort (SQL Server) to something else? Or do I need to set the locale in DataAdaoter / DataTable (ADO.NET)?
Thanks in advance to everyone who will help
a source to share
Yes, the problem is most likely in the comparison. The summary Latin1_General
does not include rules for sorting and comparing non-Latin characters.
MSDN states:
If you must store character data that reflects multiple languages, you can minimize collation compatibility issues by always using the nchar, nvarchar, and ntext Unicode data types instead of char, varchar, text data types. Using Unicode data types eliminates code page conversion issues.
Since you've already done this, you should read further information on mixed sorting environments here .
Also, I want to add that just changing the collation is not easy, check MSDN for SQL 2000:
It is important to use the correct collation when configuring SQL Server 2000. You can change the collation after running the installer, but you must rebuild the databases and reload the data. It is recommended that you develop a standard for these options in your organization. Many server-to-server operations can fail if collation is not consistent between servers.
However, you can specify column sorting:
CREATE TABLE TestTable (
id int,
GreekColCaseInsensitive nvarchar(10) collate greek_ci_as,
LatinColCaseSensitive nvarchar(10) collate latin1_general_cs_as
)
Take a look at the various binary multilingual collations here . Depending on the encoding used, you should find one that suits your purpose.
If you cannot or want to change the collation of the column, you can also simply specify the collation to be used in a query like:
SELECT * From TestTable
WHERE GreekColCaseInsensitive = N'test - ۓےۑ'
COLLATE latin1_general_cs_as
As jfrobishow specified use N before the line you want to use for comparison is important. What it does:
This means that the subsequent line is in Unicode (actually N stands for the national language character set). This means that you are passing an NCHAR, NVARCHAR, or NTEXT value, as opposed to char, VARCHAR, or TEXT. See Article # 2354 for a comparison of these data types.
You can find a short description here .
a source to share