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

User · Answer

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

User · Answer

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 · Answer

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  lt  lt  INSERT COLATION REQUIRED  gt  gt      GO    --Verify the collation setting    SELECT name  collation name   FROM sys databases   WHERE name   N  lt  lt  INSERT DATABASE NAME  gt  gt      GO    Reference here

User · Answer

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

User · Answer

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 -

User · Answer

You could easily do this by using 4 easy steps   backup your database  just incase change database collation  right click database  select properties  go to the options and change the collation to the required collation  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  amp  Create on the Advanced options of the Wizard  Also select Schema  amp  Data   Run the Script Generated above

User · Answer

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   Marc  UPDATE   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

User · Answer

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

User · Answer

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 · Answer

ALTER DATABASE test2            --put your database name here COLLATE Latin1 General CS AS    --replace with the collation you need

User · Answer

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

User · Answer

I do the following      WHERE      fieldname COLLATE DATABASE DEFAULT   otherfieldname COLLATE DATABASE DEFAULT   Works every time

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This can easily happen when you have 2 different databases and specially 2 different databases from 2 different servers  Best option is to change it to a common collection 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

User · Answer

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

User · Answer

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

User · Answer

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 · Answer

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  amp  reenable DDL triggers Ignore cryptographic provider file path Ignore File  amp  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 · Answer

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 · Answer

Check the level of collation that is mismatched  server  database table column character     If it is the server  these steps helped me once    Stop the server Find your sqlservr exe tool Run this command   sqlservr -m -T4022 -T3659 -s name of insance   -q  name of collation  Start your sql server   net start name of instance Check the collation of your server again    Here is more info   https   www mssqltips com sqlservertip 3519 changing-sql-server-collation-after-installation

User · Answer

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

[sql-server] Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation

Examples related to sql-server