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

Question

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.

Answer 1

This can easily happen when you have 2 different databases and especially 2 different databases from 2 different servers. The best option is to change it to a common collation and do the join or comparison.

SELECT 
   *
FROM sd
INNER JOIN pd ON sd.SCaseflowID COLLATE Latin1_General_CS_AS = pd.PDebt_code COLLATE Latin1_General_CS_AS

Answer 2

You have a mismatch of two different collations in your table. You can check what collations each column in your table(s) has by using this query:

SELECT
    col.name, col.collation_name
FROM 
    sys.columns col
WHERE
    object_id = OBJECT_ID('YourTableName')

Collations are needed and used when ordering and comparing strings. It's generally a good idea to have a single, unique collation used throughout your database - don't use different collations within a single table or database - you're only asking for trouble....

Once you've settled for one single collation, you can change those tables / columns that don't match yet using this command:

ALTER TABLE YourTableName
  ALTER COLUMN OffendingColumn
    VARCHAR(100) COLLATE Latin1_General_CI_AS NOT NULL

To find the fulltext indices in your database, use this query here:

SELECT
    fti.object_Id,
    OBJECT_NAME(fti.object_id) 'Fulltext index',
    fti.is_enabled,
    i.name 'Index name',
    OBJECT_NAME(i.object_id) 'Table name'
FROM 
    sys.fulltext_indexes fti
INNER JOIN 
    sys.indexes i ON fti.unique_index_id = i.index_id

You can then drop the fulltext index using:

DROP FULLTEXT INDEX ON (tablename)

Answer 3

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

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

5. 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

6. 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)

Answer 4

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).

Answer 5

ALTER DATABASE test2            --put your database name here
COLLATE Latin1_General_CS_AS    --replace with the collation you need

Answer 6

@Valkyrie awesome answer. Thought I put in here a case when performing the same with a subquery insides a stored procedure, as I wondered if your answer works in this case, and it did awesome.

...WHERE fieldname COLLATE DATABASE_DEFAULT in (
          SELECT DISTINCT otherfieldname COLLATE DATABASE_DEFAULT
          FROM ...
          WHERE ...
        )

Answer 7

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.

Answer 8

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.

Answer 9

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)

Answer 10

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

Answer 11

INSERT INTO eSSLSmartOfficeSource2.[dbo].DeviceLogs  (DeviceId,UserId,LogDate,UpdateFlag) 
SELECT DL1.DeviceId ,DL1.UserId COLLATE DATABASE_DEFAULT,DL1.LogDate 
,0 FROM eSSLSmartOffice.[dbo].DeviceLogs DL1 
WHERE  NOT EXISTS 
(SELECT DL2.DeviceId ,DL2.UserId COLLATE DATABASE_DEFAULT
,DL2.LogDate ,DL2.UpdateFlag 
FROM eSSLSmartOfficeSource2.[dbo].DeviceLogs DL2    
WHERE  DL1.DeviceId =DL2.DeviceId
 and DL1.UserId collate  Latin1_General_CS_AS=DL2.UserId collate  Latin1_General_CS_AS
  and DL1.LogDate =DL2.LogDate )

Answer 12

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

Answer 13

error (Cannot resolve the collation conflict between .... ) usually occurs while comparing data from multiple databases.

since you cannot change the collation of databases now, use COLLATE DATABASE_DEFAULT.

----------
AND db1.tbl1.fiel1 COLLATE DATABASE_DEFAULT =db2.tbl2.field2 COLLATE DATABASE_DEFAULT 

Answer 14

Check the level of collation that is mismatched (server, database,table,column,character).

If it is the server, these steps helped me once:

1. Stop the server
2. Find your sqlservr.exe tool

3. Run this command:

   sqlservr -m -T4022 -T3659 -s"name_of_insance" -q "name_of_collation"

4. Start your sql server:

   net start name_of_instance

5. Check the collation of your server again.

Here is more info:

https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation/

Answer 15

You could easily do this by using 4 easy steps

1. backup your database, just incase
2. change database collation: right click database, select properties, go to the options and change the collation to the required collation.
3. Generate a script to Drop and Recreate all your database objects: right click your database, select tasks, select generate script... ( make sure you select Drop & Create on the Advanced options of the Wizard, Also select Schema & Data )
4. Run the Script Generated above

Answer 16

I have used the content from this site to create the following script which changes collation of all columns in all tables:

CREATE PROCEDURE [dbo].[sz_pipeline001_collation] 
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;


SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' +
SYSTYPES.name + 
    CASE systypes.NAME
    WHEN 'text' THEN ' '
    ELSE
    '(' + RTRIM(CASE SYSCOLUMNS.length
    WHEN -1 THEN 'MAX'
    ELSE CONVERT(CHAR,SYSCOLUMNS.length)
    END) + ') ' 
    END

    + ' ' + ' COLLATE Latin1_General_CI_AS ' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
    FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
    WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
    AND SYSOBJECTS.TYPE = 'U'
    AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
    AND SYSCOLUMNS.COLLATION IS NOT NULL
    AND NOT ( sysobjects.NAME LIKE 'sys%' )
    AND NOT ( SYSTYPES.name LIKE 'sys%' )

END

Answer 17

I do the following:

...WHERE 
    fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT

Works every time. :)

Answer 18

Thanks to marc_s's answer I solved my original problem - inspired to take it a step further and post one approach to transforming a whole table at a time - tsql script to generate the alter column statements:

DECLARE @tableName VARCHAR(MAX)
SET @tableName = 'affiliate'
--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) + ') '
          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)

gets: ALTER TABLE Affiliate ALTER COLUMN myTable NVARCHAR(4000) COLLATE Latin1_General_CI_AS NOT NULL

I'll admit to being puzzled by the need to col.max_length / 2 -

Answer 19

Identify the fields for which it is throwing this error and add following to them: COLLATE DATABASE_DEFAULT

There are two tables joined on Code field:

...
and table1.Code = table2.Code
...

Update your query to:

...
and table1.Code COLLATE DATABASE_DEFAULT = table2.Code COLLATE DATABASE_DEFAULT
...

Answer 20

here is what we did, in our situation we need an ad hoc query to be executed using a date restriction on demand, and the query is defined in a table.

Our new query needs to match data between different databases and include data from both of them.

It seems that the COLLATION is different between the db that imports data from the iSeries/AS400 system, and our reporting database - this could be because of the specific data types (such as Greek accents on names and so on).

So we used the below join clause:

...LEFT Outer join ImportDB..C4CTP C4 on C4.C4CTP COLLATE Latin1_General_CS_AS=CUS_Type COLLATE Latin1_General_CS_AS

Answer 21

I had a similar error (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1250_CI_AS" in the INTERSECT operation), when I used old jdbc driver.

I resolved this by downloading new driver from Microsoft or open-source project jTDS.

Answer 22

Use the collate clause in your query:

LEFT JOIN C tO_C on tA.FieldName = 'CID' AND tA.oldValue COLLATE Latin1_General_CI_AS = tO_C.Name  

I may not have the syntax exactly right (check BOL), but you can do this to change the collation on-the-fly for the query - you may need to add the clause for each join.

edit: I realized this was not quite right - the collate clause goes after the field you need to change - in this example I changed the collation on the tA.oldValue field.

Answer 23

For those who have a CREATE DATABASE script (as was my case) for the database that is causing this issue you can use the following CREATE script to match the collation:

-- Create Case Sensitive Database
CREATE DATABASE CaseSensitiveDatabase
COLLATE SQL_Latin1_General_CP1_CS_AS -- or any collation you require
GO
USE CaseSensitiveDatabase
GO
SELECT *
FROM sys.types
GO
--rest of your script here

or

-- Create Case In-Sensitive Database
CREATE DATABASE CaseInSensitiveDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS -- or any collation you require
GO
USE CaseInSensitiveDatabase
GO
SELECT *
FROM sys.types
GO
--rest of your script here

This applies the desired collation to all the tables, which was just what I needed. It is ideal to try and keep the collation the same for all databases on a server. Hope this helps.

More info on the following link: SQL SERVER – Creating Database with Different Collation on Server

Answer 24

The root cause is that the sql server database you took the schema from has a collation that differs from your local installation. If you don't want to worry about collation re install SQL Server locally using the same collation as the SQL Server 2008 database.

Answer 25

I have had something like this before, and what we found was that the collation between 2 tables were different.

Check that these are the same.