“ 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

问题描述

我正在创建从SQL SERVER 2008 R2到WINDOWS AZURE的SQL函数，但是我没有解决该问题的方法。

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

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

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.

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.

推荐答案

您在数据库排序规则（@ RtnValue.Data）与数据库排序规则之间存在排序规则不匹配的情况。

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

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

Quickest solution may be to explicitly declare the collation in @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.

这篇关于“ SQL_Latin1_General_CP1_CI_AS”之间的排序规则冲突和“ Modern_Spanish_CI_AS”等于操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！