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.
