[转]SQL Collation冲突解决临时表

2022-09-29 10:32:32

本文转自：http://ju.outofmemory.cn/entry/191163

问题描述

在SQL Server中使用一些复杂的存储过程时，我们需要借用临时表来完成一些逻辑的处理，例如：数据的临时存储、循环处理等等。
临时表创建后，并不是在各个数据库中存在的，而是存在于系统数据库tempdb中。
如今在一个包含临时表的存储过程中，我们遇到了SQL Collation冲突的错误：

错误信息：

Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

检查我们的应用数据库和tempdb的Collation，发现这两个数据库的Collation是不一致的。

应用数据库的Collation是：SQL_Latin1_General_CP1_CI_AS

tempdb的Collation是：Chinese_PRC_CI_AS

解决方法

通过GUI方式修改

tempdb作为系统的数据库，它的Collation是不可以被更改的，我们可以更改应用数据库的Collation来解决这个问题。

在数据库的Properties → Options有更改数据库的Collation选项，但修改时会出现错误：

通过SQL脚本修改

SQL脚本也可以修改数据库的Collation，但是要在SINGLE_USER WITH ROLLABACK IMMEDIATE模式下修改，修改完成后再还原为MULTI_USER模式

ALTER DATABASE SampleDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE SampleDb COLLATE Chinese_PRC_CI_AS

GO

ALTER DATABASE SampleDb SET MULTI_USER

GO

执行SQL后，数据库的Collation变更为Chinese_PRC_CI_AS

]

验证Collation是否修改成功

要验证Collation是否修改成功，只需要再次执行存储过程，执行后仍然会出现Collation冲突错误：

Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

检查数据库中已创建的表字段的Collation属性，仍然是SQL_Latin1_General_CP1_CI_AS。

尝试在数据库中创建一个新的Table

CREATE TABLE Collation_Test

    (

      [ID] INT IDENTITY

               PRIMARY KEY ,

      [Name] NVARCHAR(30)

    )

GO

查看Collation_Test表字段Name的Collation属性，则是Chinese_PRC_CI_AS

结论：这说明以上修改数据库Collation的SQL脚本，确实成功修改了数据库的Collation属性，新创建的表的字段的Collation属性会继承数据库的Collation属性，但它不会影响已创建的表的字段的Collation属性。
简单地说，修改数据库的Collation，对于已存在的表没有影响，对于新创建的表有影响。

修改表字段的Collation属性

在表字段的属性中可以修改Collation属性，但这种方式每次只能更改一个，我们已经创建了很多的表，使用这种方式不太现实。

那么如何批量地修改表字段的Collation属性呢？

执行下面一段SQL可以解决这个问题：

Hide Code|

Copy Code

DECLARE @collate NVARCHAR(100);

DECLARE @table NVARCHAR(255);

DECLARE @column_name NVARCHAR(255);

DECLARE @column_id INT;

DECLARE @data_type NVARCHAR(255);

DECLARE @max_length INT;

DECLARE @row_id INT;

DECLARE @sql NVARCHAR(MAX);

DECLARE @sql_column NVARCHAR(MAX);

--期望的Collation

SET @collate = 'Chinese_PRC_CI_AS';

DECLARE local_table_cursor CURSOR

FOR

    SELECT  [name]

    FROM    sysobjects

    WHERE   OBJECTPROPERTY(id, N'IsUserTable') = 1

OPEN local_table_cursor

FETCH NEXT FROM local_table_cursor

INTO @table

WHILE @@FETCH_STATUS = 0

    BEGIN

        DECLARE local_change_cursor CURSOR

        FOR

            SELECT  ROW_NUMBER() OVER ( ORDER BY c.column_id ) AS row_id ,

                    c.name column_name ,

                    t.Name data_type ,

                    c.max_length ,

                    c.column_id

            FROM    sys.columns c

                    JOIN sys.types t ON c.system_type_id = t.system_type_id

                    LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id

                                                            AND ic.column_id = c.column_id

                    LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id

                                                     AND ic.index_id = i.index_id

            WHERE   c.object_id = OBJECT_ID(@table)

            ORDER BY c.column_id

        OPEN local_change_cursor

        FETCH NEXT FROM local_change_cursor

    INTO @row_id, @column_name, @data_type, @max_length, @column_id

        WHILE @@FETCH_STATUS = 0

            BEGIN

                IF ( @max_length = -1 )

                    SET @max_length = 4000;

                IF ( @data_type LIKE '%char%' )

                    BEGIN TRY

                        SET @sql = 'ALTER TABLE ' + @table + ' ALTER COLUMN '

                            + @column_name + ' ' + @data_type + '('

                            + CAST(@max_length AS NVARCHAR(100))

                            + ') COLLATE ' + @collate

                        PRINT @sql

                        EXEC sp_executesql @sql

                    END TRY

                    BEGIN CATCH

                        PRINT 'ERROR: Some index or constraint rely on the column'

                            + @column_name + '. No conversion possible.'

                        PRINT @sql

                    END CATCH

                FETCH NEXT FROM local_change_cursor

        INTO @row_id, @column_name, @data_type, @max_length, @column_id

            END

        CLOSE local_change_cursor

        DEALLOCATE local_change_cursor

        FETCH NEXT FROM local_table_cursor

    INTO @table

    END

CLOSE local_table_cursor

DEALLOCATE local_table_cursor

GO

查看已存在的表的字段的Collation属性，已经成功修改为Chinese_PRC_CI_AS了。

并不是数据库中所有表字段的Collation都更改成功了，从输出结果中我们可以发现一些Error信息。

字符串类型(varchar,char,nchar,nvarchar..)主键字段的Collation没有被更改。

查询数据库中哪些表的字段Collation属性不是Chinese_PRC_CI_AS的字段：

Hide Code|

Copy Code

DECLARE @collate SYSNAME

SELECT  @collate = 'Chinese_PRC_CI_AS'

SELECT  '[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name ,

        'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']

        ALTER COLUMN [' + c.name + '] ' + UPPER(t.name)

        + CASE WHEN t.name NOT IN ( 'ntext', 'text' )

               THEN '('

                    + CASE WHEN t.name IN ( 'nchar', 'nvarchar' )

                                AND c.max_length != -1

                           THEN CAST(c.max_length / 2 AS VARCHAR(10))

                           WHEN t.name IN ( 'nchar', 'nvarchar' )

                                AND c.max_length = -1 THEN 'MAX'

                           ELSE CAST(c.max_length AS VARCHAR(10))

                      END + ')'

               ELSE ''

          END + ' COLLATE ' + @collate

        + CASE WHEN c.is_nullable = 1 THEN ' NULL'

               ELSE ' NOT NULL'

          END

FROM    sys.columns c WITH ( NOLOCK )

        JOIN sys.objects o WITH ( NOLOCK ) ON c.[object_id] = o.[object_id]

        JOIN sys.types t WITH ( NOLOCK ) ON c.system_type_id = t.system_type_id

                                            AND c.user_type_id = t.user_type_id

WHERE   t.name IN ( 'char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar' )

        AND c.collation_name != @collate

        AND o.[type] = 'U'

删除主键

批量更改字段的Collation属性时，由于主键约束的存在，导致字符串类型的主键Collation无法被修改。
但我们可以批量删除主键。

Hide Code|

Copy Code

--包含主键的表名

DECLARE @tableName VARCHAR(100)

--动态SQL，用于DROP表的主键

DECLARE @sql NVARCHAR(1024)

--定义游标

DECLARE curse CURSOR

FOR

	--查询包含主键的表

    SELECT  T2.name

    FROM    sys.key_constraints T1 ,

            sys.sysobjects T2

    WHERE   T1.type = 'PK'

            AND T2.type = 'U'

            AND CHARINDEX(t2.name, t1.name, 0) >= 1

--打开游标

OPEN curse;

FETCH NEXT FROM curse INTO @tableName;

--循环游标

WHILE @@fetch_status = 0

    BEGIN

		--拼接SQL

        SELECT  @sql = 'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT '

                + name + ';'

        FROM    sys.key_constraints

        WHERE   [type] = 'PK'

                AND [parent_object_id] = OBJECT_ID(@tableName);

		--打印

        PRINT @sql

		--执行动态SQL

        EXEC(@sql)

        FETCH NEXT FROM curse INTO @tableName;

    END

--关闭游标

CLOSE curse;

--释放游标

DEALLOCATE curse;

删除主键后，再次执行批量更改字段Collation的语句。

总结

经过以上的反复折腾，我们终于将数据库以及表字段的Collation更改过来了，这是”事后“解决方案，并非最佳的解决方案。

更佳的解决方案应该是”事前“解决方案：

在开发开始之前，弄清楚客户的数据库环境，包括tempdb的collation和用户自定义数据库的默认collation。该项任务属于需求阶段的任务，但需要开发者的参与。
在开发环境准备时，尽量使用和客户环境一致的开发环境，包括服务器版本、服务器设定、数据库版本、数据库设定
开发开始时，先不要进行大规模的开发，而是全方位的再次确认开发环境。
对于本文中出现的问题，可以通过实施一些数据库的Test Case来解决。

参考

http://*.com/questions/16730114/how-to-change-the-collate-to-all-the-columns-of-the-database

http://*.com/questions/18122773/change-collations-of-all-columns-of-all-tables-in-sql-server

http://*.com/questions/13948344/drop-primary-key-using-script-in-sql-server-database

码农公寓

问题描述

解决方法

通过GUI方式修改

通过SQL脚本修改

验证Collation是否修改成功

修改表字段的Collation属性

删除主键

总结

参考

相关文章