Friday, January 6, 2012

Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.


Sql server Collation Problem and Fix
What is collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying
  • case-sensitivity,
  • accent marks,
  • kana character types and
  • character width.
Case sensitivity
If A and a, B and b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.
Accent sensitivity
If a and á, o and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and áis 225. The ASCII value of o is 111 and ó is 243.
Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
Width sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.
Production server collation error(Log):
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Reproduce this error in local QA environment:
How to exploring collation?

Step 1 : What Collation is SQL installed local server identify below query
SELECT SERVERPROPERTY('COLLATION')

Step 2 : Identify databases level Collation
SELECT NAME,COLLATION_NAME FROM SYS.DATABASES
Step 4 : Show the collation for each column in selected database level
SELECT
C.TABLE_CATALOG AS DATABASE_NAME
,C.TABLE_SCHEMA
,C.TABLE_NAME
,C.COLUMN_NAME
,DATA_TYPE
,SERVERPROPERTY('COLLATION') AS SERVER_COLLATION
,CONVERT(SYSNAME,DATABASEPROPERTYEX(D.NAME,'COLLATION')) AS DATABASE_COLLATION
,C.COLLATION_NAME AS COLUMN_COLLATION
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN SYS.DATABASES D
ON DB_ID(C.TABLE_CATALOG) = DB_ID(D.NAME)
WHERE DATA_TYPE IN ('VARCHAR' ,'CHAR','NVARCHAR','NCHAR','TEXT','NTEXT')
In local server level collation ,database level collation and column level collation are same collation (“SQL_Latin1_General_CP1_CI_AS”)
How to Reproduce local environment?
Step 1:Change the database level collation using the below query
ALTER DATABASE FP_Yeti_QA2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE FP_Yeti_QA2 COLLATE Latin1_General_CI_AI
GO
ALTER DATABASE FP_Yeti_QA2 SET MULTI_USER

Latin1_General_CI_AI This is not sql server collation ,This is Windows level collation existing in production server, That why I changed in local server also same collation.

Step 2 :Change table level collation means all column collation using the below script. ( The script will generate all table column collation querys )

DECLARE @Table_Name NVARCHAR(100)
SET @Table_Name = NULL--- THIS IS THE TableName that you want to change its collation columns
--- if null will set to all tables
DECLARE @TempTable AS TABLE
(
ID INT IDENTITY
,TableName NVARCHAR(100)
,ColumnName NVARCHAR(100)
,TypeName NVARCHAR(100)
,Max_length INT
,Collation_Name NVARCHAR(100)
,EnterDtm DATETIME DEFAULT GETDATE()
)
DECLARE @NewCollation NVARCHAR(100)
SET @NewCollation = 'database_default' --- THIS IS THE COLLATION NAME THAT YOU WANT TO CHANGE

INSERT INTO @TempTable(TableName,ColumnName,TypeName,Max_length,Collation_Name)
SELECT
QUOTENAME(SCHEMA_NAME(tables.schema_id)) + '.' + QUOTENAME(tables.name) AS TableName
,all_columns.name AS ColumnName
,type_name(all_columns.user_type_id)
,all_columns.max_length
,all_columns.collation_name
from sys.all_columns INNER JOIN sys.tables ON
tables.object_id = all_columns.object_id
AND collation_name IS NOT NULL
AND all_columns.collation_name != @NewCollation
WHERE tables.object_id = ISNULL(object_id(@Table_Name),all_columns.object_id)

DECLARE @TableID SMALLINT
SET @TableID = (SELECT MIN(ID) FROM @TempTable)

DECLARE @Query NVARCHAR(1000),@TableName NVARCHAR(100),@ColumnName NVARCHAR(100),@TypeName NVARCHAR(100)
,@Size INT

WHILE @TableID IS NOT NULL
BEGIN
SET @TableName = (SELECT TableName FROM @TempTable WHERE ID = @TableID)
SET @ColumnName = (SELECT QUOTENAME(ColumnName) FROM @TempTable WHERE ID = @TableID)
SET @TypeName = (SELECT TypeName FROM @TempTable WHERE ID = @TableID)
SET @Size = (SELECT Max_length FROM @TempTable WHERE ID = @TableID)

SET @Query='ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @TypeName+ ISNULL ('(' +CAST(@Size AS VARCHAR(200))+')', '') +' COLLATE '+ @NewCollation
PRINT (@Query)
--execute sp_executesql @Query
SET @TableID = (SELECT MIN(ID) FROM @TempTable WHERE ID > @TableID)
END

This is the way to generate script all table in select database level and execute single usermode
Ex:-
ALTER DATABASE FP_Yeti_QA2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Here column level generating scripts
GO
ALTER DATABASE FP_Yeti_QA2 SET MULTI_USER

How to Error Fix:
Step 1:Change the database level collation using the below query
ALTER DATABASE FP_Yeti_QA2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE FP_Yeti_QA2 COLLATE SQL_Latin1_General_CP1_CI_AS
GO
ALTER DATABASE FP_Yeti_QA2 SET MULTI_USER

SQL_Latin1_General_CP1_CI_AS this is sql server default collation all system databases follow the same collation better we should follow user databases also.

Step 2 :Change table level collation means all column collation inherit the database level collation (database_default) using the below script. ( The script will generate all table column collation querys )

DECLARE @Table_Name NVARCHAR(100)
SET @Table_Name = NULL--- THIS IS THE TableName that you want to change its collation columns
--- if null will set to all tables
DECLARE @TempTable AS TABLE
(
ID INT IDENTITY
,TableName NVARCHAR(100)
,ColumnName NVARCHAR(100)
,TypeName NVARCHAR(100)
,Max_length INT
,Collation_Name NVARCHAR(100)
,EnterDtm DATETIME DEFAULT GETDATE()
)
DECLARE @NewCollation NVARCHAR(100)
SET @NewCollation = 'database_default' --- THIS IS THE COLLATION NAME THAT YOU WANT TO CHANGE

INSERT INTO @TempTable(TableName,ColumnName,TypeName,Max_length,Collation_Name)
SELECT
QUOTENAME(SCHEMA_NAME(tables.schema_id)) + '.' + QUOTENAME(tables.name) AS TableName
,all_columns.name AS ColumnName
,type_name(all_columns.user_type_id)
,all_columns.max_length
,all_columns.collation_name
from sys.all_columns INNER JOIN sys.tables ON
tables.object_id = all_columns.object_id
AND collation_name IS NOT NULL
AND all_columns.collation_name != @NewCollation
WHERE tables.object_id = ISNULL(object_id(@Table_Name),all_columns.object_id)

DECLARE @TableID SMALLINT
SET @TableID = (SELECT MIN(ID) FROM @TempTable)

DECLARE @Query NVARCHAR(1000),@TableName NVARCHAR(100),@ColumnName NVARCHAR(100),@TypeName NVARCHAR(100)
,@Size INT

WHILE @TableID IS NOT NULL
BEGIN
SET @TableName = (SELECT TableName FROM @TempTable WHERE ID = @TableID)
SET @ColumnName = (SELECT QUOTENAME(ColumnName) FROM @TempTable WHERE ID = @TableID)
SET @TypeName = (SELECT TypeName FROM @TempTable WHERE ID = @TableID)
SET @Size = (SELECT Max_length FROM @TempTable WHERE ID = @TableID)

SET @Query='ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @TypeName+ ISNULL ('(' +CAST(@Size AS VARCHAR(200))+')', '') +' COLLATE '+ @NewCollation
PRINT (@Query)
--execute sp_executesql @Query
SET @TableID = (SELECT MIN(ID) FROM @TempTable WHERE ID > @TableID)
END

Ex:-
ALTER DATABASE FP_Yeti_QA2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Here column level generating scripts
GO
ALTER DATABASE FP_Yeti_QA2 SET MULTI_USER

Restart the database server.
Posted by at No comments:
Subscribe to: Posts (Atom)