关于解决SQLServer大小写敏感的办法
发布时间：2020-12-12 15:32:32 所属栏目：MsSql教程 来源：网络整理
导读：原因： 在运行一个纬度抽取调度包时出现错误提示，通过定位发现 p_dem_XXX存储过程运行失败。 经诊断，发现与字符集的安装和程序息息相关！ 分析： 维度抽取失败通常的情况下是维度表结构 t_dem_XXX和业务系统基础表结构 t_pub_XXX不一致，或者为字段长度不
|
原因：
在运行一个纬度抽取调度包时出现错误提示，通过定位发现 p_dem_XXX存储过程运行失败。 经诊断，发现与字符集的安装和程序息息相关！ 分析： 维度抽取失败通常的情况下是维度表结构 t_dem_XXX和业务系统基础表结构 t_pub_XXX不一致，或者为字段长度不一致，或者类型不匹配，或者为主键不同，导致数据抽取时出现违反唯一性约束或字段被截取错误或者类型转换错误。 经过仔细检查发现这两个数据表结构都是一致的，并没有任何错误。然而再调试 p_dem_XXX仍发现报告说违反唯一性约束机制，检查左关联查询语句也没有发现问题。只好根据维度表结构 t_dem_XXX提供的主键情况进行重复检查，却发现许多条重复，经过仔细检查重复记录发现重复记录的字段中是不区分大小写的。然后突然想到默认安装的 SQLServer中是不区分大小写的。 解决步骤： 1. 备份各个数据库数据和脚本。 2. 通过查询 SQLServer联机帮助文件，发现可以通过重建数据库改变实例的排序规则，于是未经思考，直接按照帮助中所提到的 rebuildm 方法 (重建 master，有兴趣可以查看联机帮助文件 )对数据库进行重建，重建时选择中文，复选框选择大小写敏感，重建后发现所有的数据库均不见了，原来的工作白做了，只好重新建库。 3. 重新建库后，运行备份出的数据库脚本，却发现遇到了许多错误，运行不下去了，才想到，排序规则不仅只对字段文本的内容有效，同时也对所有数据库中所有对象 (表、视图、字段、自定义函数、存储过程，系统函数不知道情况如何 )均有效，没办法只好再重建数据库修改回来。 4. 通过重建方法需要修改东西太多了，有没有更简化的操作办法呢，再次查询联机帮助发现可以修改数据库的语言选项为 COLLATE Chinese_PRC_CS_AS，表示为中文且大小写敏感，关于语言和排序规则的关系请执行 select * from ::fn_helpcollations()，结果集中有详细说明，但问题是修改数据库需要修改大量的脚本，怪就怪在我们在写脚本是不注意编程规范，进行大批量的修改脚本也不太现实。 5. 既然修改实例和数据库排序规则都不可行，有没有办法进行最小的改动呢，只修改需要区分大小写的表或者字段，于是查看了一下数据库脚本导出方法导出的 SQL脚本，发现字符性字段后面均带着 Collate Chinese_RPT_CS_AS之类的东西，发现和之前字符集排序规则中的内容差不多，就想办法去修改，调试过程如下： Create Table Test ( a varchar(20) Collate Chinese_RPT_CS_AS) on primary; Insert into Test Values(‘a’) Insert into Test Values(‘A’) Select * from Test where a = ‘a’ --结果为 a A 也可以通过设计表的方式，点击字符型字段列属性的排序规则，可以对该列的排序规则进行设置，见下图 ? 6. 心想问题总算解决了，又开始调试该存储过程，谁知道查询分析器又报告： 服务器 : 消息 446，级别 16，状态 9，行 1 Cannot resolve collation conflict for equal to operation. 注：我们的脚本一般为 select a.a,a.b,a.c from DATABASENAME..USERNAME.T_BASIC_TABLE a left outer join t_dem_XXX b on a.a=b.a and a.b=b.b where …. 注：关于数据库链接和引用表一定要使用大写，我就犯了类似的错误。 7. 问题又冒出来了，只好又做测试，创建了一个相同字段的表 (大小写不同 )，经测试还是提示上面问题，没办法只好又建了一个一模一样的临时表，这些测试没问题了；才想到了即使是链接过来的表在 SQLServer中默认也是不区分大小写的，尽管内容可以区分，于是想到把链接表内容直接写到新建的一模一样的临时表中，然后再用临时表和正式表进行关联，经测试 OK。 附录： SQLServer实例排序规则是无法提供脚本修改的，只能在初始安装时设定或者通过重建方式进行修改。 关于修改数据库的排序规则脚本 CREATE DATABASE [database_XXX] ON (NAME = N'BIDW_ICD_PRI_DAT',FILENAME = N'E:XXXDATAFILEdatabase_data.dat',SIZE = 100,FILEGROWTH = 100) LOG ON (NAME = N'BIDW_ICD_LOG',FILENAME = N'E:XXXLOGFILEdatabase_log.dat', SIZE = 100,FILEGROWTH = 100) COLLATE Chinese_PRC_CS_AS --原来为 Chinese_PRC_CI_AS 关于修改表中字段的排序规则脚本 Create Table Test ( a varchar(20) Collate Chinese_RPT_CS_AS --原来为 Chinese_PRC_CI_AS ) on primary; 关于查看系统字符集和排序规则的脚本 SELECT * FROM ::fn_helpcollations() ? 具体修改脚本如下： CREATE TABLE [t_dem_XXX] ( [ID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL, [Name1] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL, [Name2] [varchar] (5) COLLATE Chinese_PRC_CS_AS NOT NULL, [Name3] [numeric](4,0) NOT NULL, [Name4] [varchar] (20) COLLATE Chinese_PRC_CS_AS NOT NULL, [Name5] [varchar] (30) COLLATE Chinese_PRC_CS_AS NOT NULL ) GO （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |