Cannot resolve collation conflict between '' and '' in equal to operation

Cannot resolve collation conflict between '' and '' in equal to operation

Collations and tempdb

The tempdb database is built every time SQL Server is started and has the same default collation as the model database. This is typically the same as the default collation of the instance. If you create a user database and specify a different default collation than model, the user database has a different default collation than tempdb. All temporary stored procedures or temporary tables are created and stored in tempdb. This means that all implicit columns in temporary tables and all coercible-default constants, variables, and parameters in temporary stored procedures have collations that are different from comparable objects created in permanent tables and stored procedures.

This could lead to problems with a mismatch in collations between user-defined databases and system database objects. For example, an instance of SQL Server uses the Latin1_General_CS_AS collation and you execute the following statements:

CREATE DATABASE TestDB COLLATE Estonian_CS_AS;  
USE TestDB;  
CREATE TABLE TestPermTab (PrimaryKey int PRIMARY KEY, Col1 nchar );  

In this system, the tempdb database uses the Latin1_General_CS_AS collation with code page 1252, and TestDB and TestPermTab.Col1 use the Estonian_CS_AS collation with code page 1257. For example:

USE TestDB;  
GO  
-- Create a temporary table with the same column declarations  
-- as TestPermTab  
CREATE TABLE #TestTempTab (PrimaryKey int PRIMARY KEY, Col1 nchar );  
INSERT INTO #TestTempTab  
         SELECT * FROM TestPermTab;  
GO  

With the previous example, the tempdb database uses the Latin1_General_CS_AS collation, and TestDB and TestTab.Col1 use the Estonian_CS_AS collation. For example:

SELECT * FROM TestPermTab AS a INNER JOIN #TestTempTab on a.Col1 = #TestTempTab.Col1;  

Because tempdb uses the default server collation and TestPermTab.Col1 uses a different collation, SQL Server returns this error: "Cannot resolve collation conflict between 'Latin1_General_CI_AS_KS_WS' and 'Estonian_CS_AS' in equal to operation."

To prevent the error, you can use one of the following alternatives:

• Specify that the temporary table column use the default collation of the user database, not tempdb. This enables the temporary table to work with similarly formatted tables in multiple databases, if that is required of your system.

  CREATE TABLE #TestTempTab  
     (PrimaryKey int PRIMARY KEY,  
      Col1 nchar COLLATE database_default  
     );  
  

• Specify the correct collation for the #TestTempTab column:

  CREATE TABLE #TestTempTab  
     (PrimaryKey int PRIMARY KEY,  
      Col1 nchar COLLATE Estonian_CS_AS  
     );  

Set or Change the Column Collation

You can override the database collation for char, varchar, text, nchar, nvarchar, and ntext data by specifying a different collation for a specific column of a table and using one of the following:

• The COLLATE clause of CREATE TABLE and ALTER TABLE. For example:

  CREATE TABLE dbo.MyTable  
    (PrimaryKey   int PRIMARY KEY,  
     CharCol      varchar(10) COLLATE French_CI_AS NOT NULL  
    );  
  GO  
  ALTER TABLE dbo.MyTable ALTER COLUMN CharCol  
              varchar(10)COLLATE Latin1_General_CI_AS NOT NULL;  
  GO  
  

• SQL Server Management Studio. For more information, Collation and Unicode Support.
• Using the Column.Collation property in SQL Server Management Objects (SMO).
  You cannot change the collation of a column that is currently referenced by any one of the following:
• A computed column
• An index
• Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
• A CHECK constraint
• A FOREIGN KEY constraint
  When you work with tempdb, the COLLATE clause includes a database_default option to specify that a column in a temporary table uses the collation default of the current user database for the connection instead of the collation of tempdb.

Collations and text Columns

You can insert or update values in a text column whose collation is different from the code page of the default collation of the database. SQL Server implicitly converts the values to the collation of the column.

Change Collation of a SQL Server Table Column using T-SQL:

First find current column collation:

USE AdventureWorks
GO
Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'col_test'
AND COLUMN_NAME = 'Text'




-------------- Disable all constraints for database

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

----------------------- Enable all constraints for database

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

---------To find column name of collation

Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME, DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = 'dbo'

AND TABLE_NAME = 'col_test

----Change Collation of a SQL Server Table Column:

USE databasename

GO

ALTER TABLE [dbo].[col_test]

ALTER COLUMN Text NVARCHAR(10)

COLLATE SQL_Latin1_General_CP1_CS_AS NULL

To change the database collation

1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute. This example shows how to use the COLLATEclause in an ALTER DATABASE statement to change the collation name. Execute the SELECT statement to verify the change.

SQL

USE master;  
GO  
ALTER DATABASE MyOptionsTest  
COLLATE French_CI_AS ;  
GO  

--Verify the collation setting.  
SELECT name, collation_name  
FROM sys.databases  
WHERE name = N'MyOptionsTest';  
GO 


https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation/
http://zarez.net/?p=1866
http://sqlusa.com/bestpractices2005/collatedatabasedefault/