Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation

user_174892 0
Viewed 978258

I have the following code

SELECT tA.FieldName As [Field Name],
       COALESCE(tO_A.[desc], tO_B.[desc], tO_C.Name, tA.OldVAlue) AS [Old Value],
       COALESCE(tN_A.[desc], tN_B.[desc], tN_C.Name, tA.NewValue) AS [New Value],
       U.UserName AS [User Name],
       CONVERT(varchar, tA.ChangeDate) AS [Change Date] 
  FROM D tA
       JOIN 
       [DRTS].[dbo].[User] U 
         ON tA.UserID = U.UserID
       LEFT JOIN 
       A tO_A 
         on tA.FieldName = 'AID' 
        AND tA.oldValue = CONVERT(VARCHAR, tO_A.ID)
       LEFT JOIN 
       A tN_A 
         on tA.FieldName = 'AID' 
        AND tA.newValue = CONVERT(VARCHAR, tN_A.ID)
       LEFT JOIN 
       B tO_B 
         on tA.FieldName = 'BID' 
        AND tA.oldValue = CONVERT(VARCHAR, tO_B.ID)
       LEFT JOIN 
       B tN_B 
         on tA.FieldName = 'BID' 
        AND tA.newValue = CONVERT(VARCHAR, tN_B.ID)
       LEFT JOIN 
       C tO_C 
         on tA.FieldName = 'CID' 
        AND tA.oldValue = tO_C.Name
       LEFT JOIN 
       C tN_C 
         on tA.FieldName = 'CID' 
        AND tA.newValue = tN_C.Name
 WHERE U.Fullname = @SearchTerm
ORDER BY tA.ChangeDate

When running the code I am getting the error pasted in the title after adding the two joins for table C. I think this may have something to do with the fact I'm using SQL Server 2008 and have restored a copy of this db on to my machine which is 2005.

sql-server
25 Answers

In the where criteria add collate SQL_Latin1_General_CP1_CI_AS

This works for me.

WHERE U.Fullname = @SearchTerm  collate SQL_Latin1_General_CP1_CI_AS
user_80882240

To resolve this problem in the query without changing either database, you can cast the expressions on other side of the "=" sign with

COLLATE SQL_Latin1_General_CP1_CI_AS

as suggested here.

user_16377980

If this occurs across the whole of your DB then it's better to change your DB collation like so:

USE master;  
GO  
ALTER DATABASE MyOptionsTest  
COLLATE << INSERT COLATION REQUIRED >> ;  
GO  

--Verify the collation setting.  
SELECT name, collation_name  
FROM sys.databases  
WHERE name = N'<< INSERT DATABASE NAME >>';  
GO

Reference here

user_22463650

Added code to @JustSteve's answer to deal with varchar and varchar(MAX) columns:

DECLARE @tableName VARCHAR(MAX)
SET @tableName = 'first_notes'
--EXEC sp_columns @tableName
SELECT  'Alter table ' + @tableName + ' alter column ' + col.name
        + CASE ( col.user_type_id )
            WHEN 231
            THEN ' nvarchar(' + CAST(col.max_length / 2 AS VARCHAR) + ') '
            WHEN 167
            THEN ' varchar(' + CASE col.max_length 
                                WHEN -1 
                                THEN 'MAX'
                                ELSE 
                                CAST(col.max_length AS VARCHAR)
                                end
                                 + ') '
          END + 'collate Latin1_General_CI_AS ' + CASE ( col.is_nullable )
                                                    WHEN 0 THEN ' not null'
                                                    WHEN 1 THEN ' null'
                                                  END
FROM    sys.columns col
WHERE   object_id = OBJECT_ID(@tableName)
user_4200560

You may not have any collation issues in your database whatsoever, but if you restored a copy of your database from a backup on a server with a different collation than the origin, and your code is creating temporary tables, those temporary tables would inherit collation from the server and there would be conflicts with your database.

user_27217500

I had a similar requirement; documenting my approach here for anyone with a similar scenario...

Scenario

  • I have a database from a clean install with the correct collations.
  • I have another database which has the wrong collations.
  • I need to update the latter to use the collations defined on the former.

Solution

Use SQL Server Schema Comparison (from SQL Server Data Tools / Visual Studio) to compare source (clean install) with destination (the db with invalid collation).

In my case I compared the two DBs directly; though you could work via a project to allow you to manually tweak pieces in between...

  • Run Visual Studio
  • Create a new SQL Server Data Project
  • Click Tools, SQL Server, New Schema Comparison
  • Select the source database
  • Select the target database
  • Click options (⚙)
    • Under Object Types select only those types you're interested in (for me it was only Views and Tables)
    • Under General select:
      • Block on possible data loss
      • Disable & reenable DDL triggers
      • Ignore cryptographic provider file path
      • Ignore File & Log File Path
      • Ignore file size
      • Ignore filegroup placement
      • Ignore full text catalog file path
      • Ignore keyword casing
      • Ignore login SIDs
      • Ignore quoted identifiers
      • Ignore route lifetime
      • Ignore semicolon between statements
      • Ignore whitespace
      • Script refresh module
      • Script validation for new constraints
      • Verify collation compatibility
      • Verify deployment
  • Click Compare
    • Uncheck any objects flagged for deletion (NB: those may still have collation issues; but since they're not defined in our source/template db we don't know; either way, we don't want to lose things if we're only targeting collation changes). You can unchceck all at once by right clicking on the DELETE folder and selecting EXCLUDE.
    • Likewise exclude for any CREATE objects (here since they don't exist in the target they can't have the wrong collation there; whether they should exist is a question for another topic).
    • Click on each object under CHANGE to see the script for that object. Use the diff to ensure that we're only changing the collation (anything other differences manually detected you'll likely want to exclude / handle those objects manually).
  • Click Update to push changes

This does still involve some manual effort (e.g. checking that you're only impacting the collation) - but it handles dependencies for you.

Also you can keep a database project of the valid schema so you can use a universal template for your DBs should you have more than 1 to update, assuming all target DBs should end up with the same schema.

You can also use find/replace on the files in a database project should you wish to mass amend settings there (e.g. so you could create the project from the invalid database using schema compare, amend the project files, then toggle the source/target in the schema compare to push your changes back to the DB).

user_3618420

I read practically every answer and comment here so far. It got me to an easy solution by combining the responses made. So here is how it was easy for me to resolved:

  1. Create a script of the database. Right-click database > Tasks > Generate Script. Be sure to include Schema and Data
  2. Delete the database after you have saved the script. Right-click database > Delete
  3. Remove the part of the script that will recreate the database, ie, delete everything upto the first line that starts with:

USE < DATABASENAME >

GO

  1. Create the database 'manually', ie, right-click on Tables > Create database...

  2. Run the script that sets the default collation that you want for the new empty database.

    USE master;
    GO
    ALTER DATABASE << DatabaseName >>

    COLLATE << INSERT COLATION REQUIRED >> ;
    GO

  3. Run the script you saved to recreate the database

Credit to

  1. @Justin for providing the script to check the collation on the database, and how to update it
  2. @RockScience for mentioning that the change on collation will only apply to new tables/objects
  3. @Felix Mwiti Mugambi (acknowledging my fellow Kenyan :) ) for indicating the need to recreate the database. (I usually avoid dropping and creating for complex databases)
user_19096890
Related