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

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.

  • Storing a Dictionary<int,string> or KeyValuePair in a database
  • executing stored requests in a table and getting the values into a variable
  • SQL Server: What's the limit on number of UNIONs?
  • What is the difference between SET xact_abort ON and try/catch block with Transaction handling in sqlserver 2005?
  • SSRS - Group_Concat Equivalent using an Expression?
  • Locking Row in SQL 2005-2008
  • 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.

    3 Solutions collect form web for “Collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Modern_Spanish_CI_AS” in the equal to operation”

    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.

    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.

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.