Sql – Collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Modern_Spanish_CI_AS” in the equal to operation

collationsqlsql serversql-server-2008stored-procedures

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

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.

Best Answer

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

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.

Related Topic