SQL 等于操作中“SQL_Latin1_General_CP1_CI_AS”和“Modern_Spanish_CI_AS”之间的排序规则冲突

Collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Modern_Spanish_CI_AS" in the equal to operation

提问by Darf Zon

I was creating this SQL function from SQL SERVER 2008 R2 to WINDOWS AZURE, but I don't how to resolve this problem.

我正在从 SQL SERVER 2008 R2 到 WINDOWS AZURE 创建这个 SQL 函数，但我不知道如何解决这个问题。

Msg 468, Level 16, State 9, Procedure GetObjectivesByTest, Line 69 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Modern_Spanish_CI_AS" in the equal to operation.

消息 468，级别 16，状态 9，过程 GetObjectivesByTest，第 69 行无法解决等于操作中“SQL_Latin1_General_CP1_CI_AS”和“Modern_Spanish_CI_AS”之间的排序规则冲突。

CREATE FUNCTION [dbo].[GetObjectivesByTest](@testId smallint)
RETURNS 
@res TABLE 
(
    -- Add the column definitions for the TABLE variable here
    ObjectiveId smallint NOT NULL,
    Name nvarchar(50) NOT NULL,
    Expectations nvarchar(400) NULL,
    [Level] nvarchar(5) NOT NULL,
    ParentId smallint NULL,
    LearningSystem nvarchar(30) NULL,
    [Rank] tinyint NULL
)
AS
BEGIN
DECLARE @string VARCHAR(MAX)
SELECT @string = OBJECTIVES FROM TESTS WHERE TestId = @testId

DECLARE @temp TABLE
(  
  ColumnA NVARCHAR(50),
  ColumnB NVARCHAR(500),
  ID INT IDENTITY(1,1)
)

INSERT INTO @temp (ColumnA, ColumnB) VALUES ('', @string)

DECLARE @idx INT, @cnt INT
SET @idx = 1
SELECT @cnt = COUNT(*) FROM @temp

DECLARE @SplitStr nvarchar(1000),
        @SplitChar nvarchar(5), 
        @Columns VARCHAR(50)
SET @SplitChar = ','

WHILE @idx <= @cnt BEGIN
      SELECT @SplitStr = ColumnB
      FROM @temp
      WHERE id = @idx

      DECLARE @RtnValue table 
      (
        ColumnName VARCHAR(50),
        Data VARCHAR(50)
      ) 

      Declare @Count int
      Set @Count = 1

      While (Charindex(@SplitChar,@SplitStr)>0) Begin
        Insert Into @RtnValue (ColumnName,Data)
        Select @Columns, Data = ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1))) 

        Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr))
        Set @Count = @Count + 1
      End

      Insert Into @RtnValue (ColumnName,Data)

      Select @Columns,Data = ltrim(rtrim(@SplitStr))
      SET @idx = @idx + 1 
END

INSERT @RES   // here is appointing the error
SELECT C.*
FROM Objectives AS C
INNER JOIN OBJECTIVES AS B ON (C.ParentId = B.ObjectiveId)
INNER JOIN OBJECTIVES AS A ON (B.ParentId = A.ObjectiveId)
where C.Rank = 3 AND B.Rank = 2 AND A.Rank = 1 AND
      A.LearningSystem + ' ' + A.Level + '.' + C.Level IN (SELECT Data FROM @RtnValue)

    RETURN 
END

I didn't have idea about this problem, how can I fix that incompatibility. Thanks in advance.

我不知道这个问题，我该如何解决不兼容问题。提前致谢。

回答by devio

You have a collation mismatch between the database collation (@RtnValue.Data) and the collation used in Objectives.LearningSysten.

数据库排序规则 (@RtnValue.Data) 和 Objectives.LearningSysten 中使用的排序规则之间存在排序规则不匹配。

Quickest solution may be to explicitly declare the collation in @RtnValue:

最快的解决方案可能是在 @RtnValue 中显式声明排序规则：

DECLARE @RtnValue table
(
    ColumnName VARCHAR(50),
    Data VARCHAR(50) COLLATE [insert required collation name]
)

This is a quick fix, however, you should check correct use of collations on database and table column level.

这是一个快速修复，但是，您应该在数据库和表列级别检查排序规则的正确使用。

回答by Peter

Collation defines how SQL Server compares string values, and is specified at various levels within SQL Server:

排序规则定义了 SQL Server 如何比较字符串值，并在 SQL Server 内的各个级别指定：

1. Server default collation: This is specified as an option when installing SQL Server, and defines the collation that will be used for any new databases, as well as that of the master and temp databases.
2. Database default collation: This is specified when creating a new database. If not specified, the server default collation will be used. This collation is used for any string valued columns (CHAR, VARCHAR, NCHAR, NVARCHAR) created in the database. This collation is also used as the default for any string valued variables and string columns in table-valued variables.
3. Column collation: This is specified at column level, and specifies the collation used for the specific column.

1. 服务器默认排序规则：这在安装 SQL Server 时指定为一个选项，并定义将用于任何新数据库以及主数据库和临时数据库的排序规则。
2. 数据库默认排序规则：这是在创建新数据库时指定的。如果未指定，将使用服务器默认排序规则。此排序规则用于在数据库中创建的任何字符串值列（CHAR、VARCHAR、NCHAR、NVARCHAR）。此排序规则还用作任何字符串值变量和表值变量中的字符串列的默认值。
3. 列排序规则：这是在列级别指定的，并指定用于特定列的排序规则。

Some things you should bear in mind as well:

你也应该记住一些事情：

1. When restoring a database onto a new server, the server will not convert the collation for the database to that of the server.
2. The default collation used varies in different contexts: Table-valued variables and variables in T-SQL use the database default, and TempDB columns use the server default.

1. 将数据库还原到新服务器上时，服务器不会将数据库的排序规则转换为服务器的排序规则。
2. 使用的默认排序规则在不同的上下文中有所不同：T-SQL 中的表值变量和变量使用数据库默认值，TempDB 列使用服务器默认值。

You cannot implicitly compare string values with different collations. While the right thing to do would be to use the correct collation across the board, there are a few simple workarounds. Here are your options, in increasing order of complexity:

您不能隐式比较具有不同排序规则的字符串值。虽然正确的做法是全面使用正确的排序规则，但有一些简单的解决方法。以下是您的选择，按复杂程度递增：

• If this is an isolated query in which a temp table string column is being compared with a corresponding value in the database, or you're in a hurry and just want to get it working, you can specify the collation in the WHEREclause. You will need to do this wherever you compare string values in the database with local variables in T-SQL queries:

  WHERE C.Rank = 3 AND B.Rank = 2 AND A.Rank = 1 
      AND A.LearningSystem + ' ' + A.Level + '.' + C.Level COLLATE SQL_Latin1_General_CP1_CI_AS IN (SELECT Data COLLATE SQL_Latin1_General_CP1_CI_AS FROM @RtnValue)
  

• Your next option, and probably the best solution, is to match the database default collation and the collation used in all string columns in the database. Altering the database collation is as simple as calling ALTER DATABASE MyDB COLLATE SQL_Latin1_General_CP1_CI_AS, as described in the MSDN Technet article Set or Change the Database Collation. What it won't do for you, however, is to change the collation of the columns in the database. You can generate a script to do this for you, however, using the system tables. I do not have SQL Server on my machine here, so I haven't been able to test this, but this will give you the general idea. Run the script, copy the results into the SQL pane and then run that.

  WITH cte AS (SELECT o.name AS TableName, c.name AS ColumnName, t.name AS TypeName, c.max_length AS MaxLen
      FROM sys.objects o INNER JOIN sys.columns c ON o.object_id = c.object_id
          INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
      WHERE o.type = 'U'
      AND t.name IN ('char', 'nchar', 'varchar', 'nvarchar'))
  SELECT 'ALTER TABLE ' + TableName + ' ALTER COLUMN ' + ColumnName + ' ' + TypeName + '('
      + CAST(CASE WHEN SUBSTRING(TypeName, 1, 1) = 'n' THEN MaxLen/2 ELSE MaxLen END AS VARCHAR) + ') COLLATE SQL_Latin1_General_CP1_CI_AS'
  FROM cte
  

• 如果这是一个独立查询，其中临时表字符串列与数据库中的相应值进行比较，或者您很着急并且只想让它工作，您可以在WHERE子句中指定排序规则。无论何时将数据库中的字符串值与 T-SQL 查询中的局部变量进行比较，都需要执行此操作：

  WHERE C.Rank = 3 AND B.Rank = 2 AND A.Rank = 1 
      AND A.LearningSystem + ' ' + A.Level + '.' + C.Level COLLATE SQL_Latin1_General_CP1_CI_AS IN (SELECT Data COLLATE SQL_Latin1_General_CP1_CI_AS FROM @RtnValue)
  

• 您的下一个选项，可能也是最好的解决方案，是匹配数据库默认排序规则和数据库中所有字符串列中使用的排序规则。更改数据库排序规则与调用一样简单ALTER DATABASE MyDB COLLATE SQL_Latin1_General_CP1_CI_AS，如 MSDN Technet 文章设置或更改数据库排序规则中所述。但是，它不会为您做的是更改数据库中列的排序规则。但是，您可以使用系统表生成一个脚本来为您执行此操作。我这里的机器上没有 SQL Server，所以我无法对此进行测试，但这会给你一个大致的想法。运行脚本，将结果复制到 SQL 窗格中，然后运行它。

  WITH cte AS (SELECT o.name AS TableName, c.name AS ColumnName, t.name AS TypeName, c.max_length AS MaxLen
      FROM sys.objects o INNER JOIN sys.columns c ON o.object_id = c.object_id
          INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
      WHERE o.type = 'U'
      AND t.name IN ('char', 'nchar', 'varchar', 'nvarchar'))
  SELECT 'ALTER TABLE ' + TableName + ' ALTER COLUMN ' + ColumnName + ' ' + TypeName + '('
      + CAST(CASE WHEN SUBSTRING(TypeName, 1, 1) = 'n' THEN MaxLen/2 ELSE MaxLen END AS VARCHAR) + ') COLLATE SQL_Latin1_General_CP1_CI_AS'
  FROM cte
  

Note that with this solution, you will still need to specify the collation for any columns used in temporary tables, whether these are in the context of a stored procedure or a raw T-SQL command. However, this is good practise, because if you deploy this database to a customer who already has their own database server and they wish to use the same server, you cannot expect them to have to change their server's default collation.

请注意，使用此解决方案，您仍需要为临时表中使用的任何列指定排序规则，无论这些列是在存储过程的上下文中还是在原始 T-SQL 命令的上下文中。但是，这是一种很好的做法，因为如果您将此数据库部署到已经拥有自己的数据库服务器的客户并且他们希望使用相同的服务器，则您不能期望他们必须更改其服务器的默认排序规则。

• Finally, you can change the server collation as well to match that of the database and all of the columns. This is a pain in the butt, but can be done using the original setup media set, as described on the MSDN Technet site at Setting and Changing the Server Collationon the MSDN Technet site.

• 最后，您还可以更改服务器排序规则以匹配数据库和所有列的排序规则。这很麻烦，但可以使用原始设置媒体集来完成，如 MSDN Technet 站点上设置和更改服务器排序规则在 MSDN Technet 站点上所述。

回答by Peter

These are ugly. I know of two ways to resolve this and neither are all that elegant:


Change the collation of one of the databases to match the other: http://msdn.microsoft.com/en-us/library/ms175835(v=sql.105).aspx

这些很丑。我知道有两种方法可以解决这个问题，但都不是那么优雅：


更改一个数据库的排序规则以匹配另一个：http: //msdn.microsoft.com/en-us/library/ms175835(v=sql. 105).aspx

Or change the collation of each of the columns in your query/table to match the destination database: http://msdn.microsoft.com/en-us/library/ms190920(v=sql.105).aspx

或者更改查询/表中每一列的排序规则以匹配目标数据库：http: //msdn.microsoft.com/en-us/library/ms190920(v=sql.105).aspx