SQL Server Problem Collation (collation) conflicts

First, write in front

Recently, the company has upgraded the development environment, the database has prepared a new server, everything is ready to begin data migration, to take the way to the existing database (existing server Windows Server 2003 + SQL Server 2005) back to Up, Then restore databse on the new server (Windows server R2 + SQL server R2) and everything seems to be going well. However, error cannot resolve the collation conflict between "Chinese_prc_ci_as" and "sql_latin1_general_cp1_ when you start accessing from Web server Ci_as "in the equal to operation.

Second, the problem analysis

The problem above is caused by collation violation (collation), which is specifically the collation violation of the language used by the database, and the collation used by the view database (old server) is as follows

And the collation of the database on the new server is SQL_Latin1_General_CP1_CI_AS (not), so you can see that the collation is different, which is where the problem lies.

So why is the collation of the database different? In other words, how did the collation of the database come about? Originally the collation here is configured/specified when the SQL Server DB instance is installed, the default collation will be based on the regional language setting of the operating system, and the regional language of our new server OS is us-english, while installing the SQL Server DB instance, we did not configure the collation (by default), which resulted in the collation of the final database as SQL_Latin1_General_CP1_CI_AS

Third, solve the problem

The following is a list of possible ways to resolve this problem and instructions

1. The collation is forcibly specified in the SQL statement, similar to the SQL statement shown below (COLLATE chinese_prc_ci_ai_ws)

SELECT a.id, b.no from TABLEA A INNER joins TABLEB B on A.name=b.name COLLATE chinese_prc_ci_ai_ws

This approach solves the problem, but it is not recommended, because once a lot of SQL statements need to be changed, it will be a disaster for the developer and not fundamentally solve the problem

2. Changing the collation of fields, tables, and databases

Script changes the collation of the database

ALTER DATABASE DBNAME COLLATE chinese_prc_ci_as

Although most of the problems can be largely resolved by changing the collation of the database level, once tempdb is used (specifically, in a temporary table, such as in store procedure), the collation problem will be faced again, And the fatal thing is that the system database master seems unable to change the collation, at least the author runs through the script when the prompt does not have permission to run, so this approach does not fundamentally solve the problem.

Description: Rebuilding a database is not a good thing, as long as Master's collation has not changed you will not be able to solve the problem.

3. Take the 2nd above, since you cannot change the collation of the master database through a script, rebuild the master database

Execute a script similar to the following

setup.exe/q/action=rebuilddatabase/instancename=mssqlserver/sapwd= "sa-pwd"/sqlsysadminaccounts= "BUILTIN\ ADMINISTRATORS "/sqlcollation=chinese_prc_ci_as

or refer to the MSDN article HTTP://SUPPORT.MICROSOFT.COM/KB/298568/ZH-CN

The author does not verify that this method is valid (the author uses the above script to run, but unsuccessful)

4. Uninstalling/Reloading An instance of SQL Server

First change the operating system's regional language to Cn-chinese, and then uninstall and reload SQL Server, changing the system's regional language settings to use the default collation when you install SQL Server (or you can, of course, not change the regional language setting, but when you install SQL When the server is manually configured/specified collation is chinese_prc_ci_as, to the collation Settings interface, the system defaults to the option of selecting the collation as the following dropdown box, which solves the problem thoroughly and is recommended.

Four, written in the last

When you do a database migration later, you should first increase the collation of the existing database, and then when you install the database on the new server, you should ensure that the collation of the original database is consistent. Extension: In fact, not only the collation of this, other likely to cause conflict or incompatibility should be a corresponding check, to upgrade or to resolve the problem before the migration, rather than leave the issue to the new server, because once the time is tight, it will be a disaster, especially the upgrade of the official library.

Reprinted from: http://www.cnblogs.com/panchunting/p/SQLServer_Issue_Collation.html

SQL Server Problem Collation (collation) conflicts