Collation conflict between “SQL_Latin1_General_CP1

Question

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.

Answer 1

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.

Answer 2

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

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

Answer 3

Collation defines how SQL Server compares string values, and is specified at various levels within 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.

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.

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 WHERE clause. 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
  

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.

• 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 Collation on the MSDN Technet site.