Company which will use the database Simplified Chinese and Traditional Chinese versions. I used this project is the traditional Chinese version of SQLServer, but need to use to another use the simplified Chinese version of SQLServer project data, I have followed the usual practice of the Simplified Chinese version of SQLServer in a data table into Traditional Chinese is used version of SQLServer, the structure and data into a success. I started and did not realize this would be a problem. Then I started to debug SQL statements is a very simple two tables associated with data selection as follows:
select table1 .*, table2 .*
from table1
inner join table2 on table2.FK = table1.PK
Logically such a simple statement that will make sense out of any problem, but in Query Analyzer which always prompts the following error
Server: Msg 446, Level 16, State 9, Line 1
Equal to action can not resolve collation conflict.
Then search for a moment and then looked at T-SQL help, only know that because I will Simplified Chinese data in SQL Server tables data into Traditional Chinese table that, together with the original data, a sort and imports, resulting in simplified form the sort of data is still simplified, so I can not compare to an error. The solution is to sort by name, told how Query Analyzer can be sorted. Here will need to use a keyword COLLATE Coliate in SQLServer Books Online is explained in:
COLLATE
A clause can be applied to a database definition or a column definition to define the sorting rules, or used in the collation string expression to apply the projection.
Syntax:
COLLATE
(Windows_collation_name) | (SQL_collation_name)
Parameter
collation_name
Is used in expressions, the definition of the column definition or database collation name. collation_name can only specify Windows_collation_name or SQL_collation_name.
Windows_collation_name
Windows collation is the collation name. See the Windows collation name.
SQL_collation_name
SQL collation is the collation name. See the SQL collation name.
So how can we know the name of the current collation is what, in fact, the name of the collation is we create the database (instance) you can choose the time, but usually we will set the original default, not its a change, so if the SQLServer Simplified Chinese Simplified Chinese will use the default sorting rules, and if the SQLServer is Traditional Chinese Simplified Chinese will use the default collation. View our databases (instances) of the property, the General tab of the bottom line is that the current collation. In the case of default, Simplified Chinese collation name is: Chinese_PRC_CI_AS, and Traditional Chinese collation name is: Chinese_Taiwan_Stroke_CI_AS, so we have simplified if the collation name of the traditional mix of time, as long as a statement about what you want to use the current kind of sorting rules can be compared, for example, that for the above SQL statement, the following two methods to solve the problem that errors
SELECT Table1 .*, Table2 .*
FROM Table1
INNER JOIN Table2 ON Table2.FK = Table1.PK COLLATE Chinese_PRC_CI_AS
SELECT Table1 .*, Table2 .*
FROM Table1
INNER JOIN Table2 ON Table2.FK = Table1.PK COLLATE Chinese_Taiwan_Stroke_CI_AS
That for a long time to solve the problems caused by the collation conflict, if there are interested, following which the SQL Server Books Online on the concept of sorting rules were posted to share with you, to save us and then go to:
Microsoft? SQL Server? 2000 supports multiple collations. Sorting rules to control the proper use of language (such as Macedonian or Polish) or alphabet (such as the Western European languages use the Latin alphabet Latin1_General) character encoding rules.
Each SQL Server collation specifies three properties:
For Unicode data types (nchar, nvarchar, and ntext) in the sort order. Sort order defined character sort sequence, and comparison operations on the characters in the value of the method.
For non-Unicode character data types (char, varchar, and text) of the sort order.
Used to store non-Unicode character data of code page.
Help can not specify the Unicode data types (nchar, nvarchar, and ntext) the corresponding code page. For double-byte Unicode characters by the Unicode standard defines bit patterns and can not be changed.
Can be specified at any level of SQL Server 2000 collation. Install SQL Server 2000 instance, can specify the default collation of the instance. Each time you create the database, may be assigned to the default collation of the database. If you do not specify the collation of the database instance default collation is the default collation. Whenever the definition of character columns, variables, or parameters, can specify the collation of these objects. If the collation is not specified, will be using the database default collation to create these objects.
If the SQL Server instance that all users use the same language, the language should be chosen to support collation. For example, if all users speak French, choose French collation.
If the SQL Server instance user languages should be chosen on multilingual needs to provide the best support for collation. For example, if a user generally speaking Western European languages, select Latin1_General collation. When support for multilingual user, all character data using Unicode data types nchar, nvarchar, and ntext is most important. Unicode aims to eliminate non-Unicode char, varchar and text data type code page conversion difficulties. Collation for comparison because the definition of the operation order and Unicode characters sort order, so when using Unicode data types to achieve all of the columns, the sorting will have different rules. Even when using Unicode data types store character data, most users should be chosen to support the collation, to prevent the use of non-Unicode data type column or variable to achieve.
Collation SQL Server database engine to store and operate the definition of character and Unicode data. However, when the data moved into the application after application for sorting and comparing characters selected by the computer control of Windows regional settings. Application uses the character data collation from the Windows Regional Settings control one of the Regional Settings also defines other projects, such as numbers, time, date and currency format. For Microsoft Windows NT? 4.0, Microsoft Windows? 98 and Microsoft Windows 95, can use the control panel "Regional Settings" application specified Windows regional settings. For Microsoft Windows 2000, use the "Control Panel" in the "Regional Options" application specified locale. The Windows regional settings for more information, please see Microsoft Web site MSDN? Page Developing International Software for Windows 95 and Windows NT 4.0.
Number of non-Unicode collation of data can use the same code page. For example, code page 1251 defines Cyrillic character set. Multiple collations (eg Cyrillic_General, Ukrainian and Macedonian) use the code page. Although these rules are the same sort of bits set to represent the non-Unicode character data, but when dealing with dictionary definitions, sorting and comparison rules apply slightly different, and dictionary definitions to determine the language or alphabet sorting rules associated with the correct characters sequence.
Because SQL Server 2000 Unicode collation control and non-Unicode sort order, so it will not encounter the non-Unicode data to Unicode and specify a different collation caused the problem. In earlier versions of SQL Server on the code page number, sort order and Unicode character collation specified separately. Earlier versions of SQL Server also supports a different number for each code page sort order, and to provide for certain code pages in Windows regional settings not the sort order. In SQL Server 7.0, you can also specify the data selection for non-Unicode sort order other than the Unicode sort order. This will result in the use and non-Unicode data relative Unicode data, sorting and comparison operations return different results.