[sql] sqlserver修改db的collation, 解决排序字规则字符集问题-4008云顶国际网站

以前用别人备份的sqlserver的srcipt在执行创建视图操作的时候，出现了如下错误, 昨天在做另一个system的batch job的时候执行一个关联语句又出现了这个错误, 第二天总结的时候发现, 要想更改db的排序排序集的时候,一定要依次将库 --表 – 列 三者的collation字符排序集都改一遍才可以。

cannot resolve the collation conflict between “japanese_ci_as” and “sql_latin1_general_cp1_ci_as” in the equal to operation.

查看修改库的collation排序字符集：因为是原来本地db中没有这个system的shcema, 是另外一个同事给我的这个schema的备份文件axx.bk文件,在通过restore database还原db后执行跨库关联语句的时候出现问题, 在遇到这个问题后的第一个意识就是去修改该还原db的collation 使其在跨库之前的表的collation保持一致

在右键查看schema的属性中发现数据库的collation是灰色无法变更的, 在这里我想到了是否有sql语句直接来修改collation：

1. alter database db_databaseset single_user with rollback immediate–修改为单用户模式
2. alter database db_databasecollate japanese_ci_as–关闭所有的查询窗口, 修改数据库排序集
3. alter database db_databaseset multi_user --再修改为多用户模式

查看修改表的collation排序集：依次执行完上面三个语句后再次查看db属性中的collation发现已经变更为预想设置的排序集, 而在修改完排序集collation后,再次执行跨库关联语句依然再次出现此问题, 查看表的排序字符集 右键表名 properties(属性) – extended(扩展属性) 中查看collation. 因为表的collation排序集依赖于库的排序字符集, 所以表的字符集已经变更japanese_ci_as

查看修改列的collation排序集：在右键查看schema的属性中发现数据库的collation是灰色无法变更的, 在这里我想到了是否有sql语句直接来修改collation：

点击右侧…按钮出现修改collation窗口如下图, 点击restore default按钮, 会自动设置成本地初始的db排序字符集

sql语句修改列的collation ：

alter column colnamenvarchar(100) collate japanese_ci_as

注意：在这里你会意识到一个问题, 只是单独修改一列, 如果要修改几十列上百列岂不是累死了.当时自己处理的方法是将该table备份为临时表之后再创建新的table导入原始数据, 另外在利用ssms的修改列的collation的时候实际上是删除这个表以及相关的约束、触发器，然后重建这个表、约束、索引、触发器.

先利用create脚本创建出来临时表之后按照下列语句导入数据, 注意不要直接使用select * into temptable from ttable, 这样创备份出来的列的collation依然是不对的。

set identity_insert temptableon
insert into tempproductrule (id, productid) select id, productid from ttable

到此为止基本关于collation的问题便得到解决, 如果是别人备份的脚本文件中带有collation在不同排序集的db中运行的时候出现错误, 也可以在相关sql语句中直接指定collation, 这样便不需要修改db相关的collation了,方法请查看：解决办法： cannot resolve the collation conflict between “japanese_ci_as” and "sql

另外一种方法是修改sql server服务器（sql server实例）的collation字符集排序,但是这种修改只能修改system系统数据库, 对于已经存在的用户数据库是不起作用的. 方法如下：

1. 在命令行cmd面板中 通过cd path 命令 进入sql server 安装文件所在目录（setup.exe文件所在目录）
2. 运行命令：net stop mssqlserver
3. setup /quiet /action=rebuilddatabase /instancename=mssqlserver /sqlsysadminaccounts=administrator pwd=xxxxxx lcollation=japanese_ci_as
4. net start mssqlserver