最近对我们的机场项目进行升级,新的系统部署后,在执行联合查询时,总会报出错误:“Cannot resolve the collation conflict between "Chinese_PRC_CS_AS" and "Chinese_PRC_CI_AS" in the equal to operation.”, 最后写了个存储过程,将新系统的表设定为区分大小写。 Chinese_PRC_CS_AS 区分大小写 Chinese_PRC_CI_AS 不区分大小写 思路是这样的:先从sqlserver的系统表 sysobjects 和 syscolums 中获取相关表的字段,然后获取该字段在sqlserver中的数据类型,长度,是否允许为空,然后对字符串类型的字段进行collate更改。 存储过程如下: Create PROCEDURE [dbo].[altercollate] AS DECLARE @colName varchar(20), @tabName varchar(20), @type varchar(20), @nullable char(1), @len varchar(21), @sqlstring nvarchar(2000), @nullStr varchar(10); DECLARE DataCursor CURSOR FOR select col.name ,col.isnullable,col.length,obj.name from syscolumns col,sysobjects obj where col.id = obj.id and (obj.xtype = 'U') AND obj.name like 'PMS%'; OPEN DataCursor; FETCH NEXT FROM DataCursor INTO @colName,@nullable,@len,@tabName; WHILE(@@FETCH_STATUS = 0) BEGIN SET @type = ''; SET @sqlstring = 'select @type = type_name(xusertype) from syscolumns where id = object_id('''+@tabName+''') and name ='''+ @colName+''';'; EXECUTE sp_executesql @sqlstring ,N'@type varchar(20) output',@type output; --IF(@tabName='PMS_DCS_STATIS') --BEGIN IF(RTRIM(LTRIM(@nullable))=0) BEGIN SET @nullStr = 'not null'; END ELSE BEGIN SET @nullStr = 'null'; END IF (RTRIM(LTRIM(@type))='char') BEGIN SET @sqlstring = 'ALTER TABLE '+@tabName +' ALTER COLUMN '+@colName+' '+@type+'('+@len+') Collate Chinese_PRC_CS_AS'+' '+@nullStr; print (@sqlstring); END ELSE IF(RTRIM(LTRIM(@type))='varchar') BEGIN SET @sqlstring = 'ALTER TABLE '+@tabName +' ALTER COLUMN '+@colName+' '+@type+'('+@len+') Collate Chinese_PRC_CS_AS'+' '+@nullStr; print (@sqlstring); END ELSE IF(RTRIM(LTRIM(@type))='nvarchar') BEGIN SET @sqlstring = 'ALTER TABLE '+@tabName +' ALTER COLUMN '+@colName+' '+@type+'('+@len+') Collate Chinese_PRC_CS_AS'+' '+@nullStr; print (@sqlstring); END ELSE BEGIN SET @sqlstring = ''; END IF(@sqlstring <> '') BEGIN EXECUTE sp_executesql @sqlstring; END --END FETCH NEXT FROM DataCursor INTO @colName,@nullable,@len,@tabName; END CLOSE DataCursor; DEALLOCATE DataCursor; |
sqlServer 大小写问题的解决方案
最新推荐文章于 2024-03-25 10:16:14 发布