Troubleshooting and Fixing Collation Conflicts
What is Collation ?
Collation controls the way string values are sorted. The default collation sorts alphabetically using the standard Latin character set. Other collations will sort in different orders. For example, a traditional Spanish collation places words beginning with "ch" at the end of a list of words beginning with "c."
It is not necessary to change the default collation to use non-Latin characters. All shared MS SQL databases support Unicode characters, allowing the storage of a variety of alphabets.
SQL Server collations control the following:
- The code page that is used to store non-Unicode data in SQL Server.
- The rules that govern how SQL Server sorts and compares characters that are stored in non-Unicode data types. SQL Server Setup will detect the Windows collation on the computer where SQL Server is being installed. Then, it will automatically select the collation for your instance of SQL Server. Sorts and comparisons might be different for Unicode and non-Unicode characters.
And if you have different collations been used on server and databases or different collations within same database then you can get Collation conflict errors such as
"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. (Microsoft SQL Server, Error: 468)"
And to troubleshoot for Collation issues, I use below steps:
--Exploring Collations
--Step 1 : What Collation is SQL installed under?
SELECT SERVERPROPERTY('COLLATION')
--Step 2 : Which collations are
available?
SELECT Name, Description FROM fn_helpcollations()
--Step 3 : Which databases have a
different collation to the server default?
SELECT
NAME AS DATABASE_NAME
, DATABASEPROPERTYEX(NAME,'COLLATION') AS DBCOLLATION
, SERVERPROPERTY('COLLATION') AS SERVERCOLLATION
FROM SYS.DATABASES
WHERE CONVERT(SYSNAME,DATABASEPROPERTYEX(NAME,'COLLATION')) <> SERVERPROPERTY('COLLATION')
--Step 4 : Show me the collation for each column in my
database
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')
--Step 5 : Show me differences in collation settings on my
server.
--This produces two results sets :
--1) Databases where collation is
different from the server setting
--2) Columns where collation is
different from the database setting
IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#CollationComparison')) DROP TABLE #CollationComparison
CREATE TABLE #CollationComparison
(Database_Name SYSNAME
,Table_Schema SYSNAME
,Table_Name SYSNAME
,Column_Name SYSNAME
,Server_Collation SYSNAME
,Database_Collation SYSNAME
,Column_Collation SYSNAME)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @dbname NVARCHAR(200)
DECLARE dbcursor CURSOR FOR
select name from sys.databases
OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
print @dbname
SET @SQL = 'INSERT INTO
#CollationComparison
(Database_Name
,Table_Schema
,Table_Name
,Column_Name
,Server_Collation
,Database_Collation
,Column_Collation)
SELECT
C.TABLE_CATALOG AS DATABASE_NAME
,C.TABLE_SCHEMA
,C.TABLE_NAME
,C.COLUMN_NAME
,CONVERT(VARCHAR,SERVERPROPERTY(''COLLATION''))
AS SERVER_COLLATION
,CONVERT(SYSNAME,DATABASEPROPERTYEX(D.NAME,''COLLATION''))
AS DATABASE_COLLATION
,C.COLLATION_NAME AS COLUMN_COLLATION
FROM [' + @dbname + '].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'')
'
exec sp_executesql @SQL
print @sql
FETCH NEXT FROM dbcursor INTO @dbname
END
CLOSE dbcursor
DEALLOCATE dbcursor
SELECT DISTINCT Server_Collation,Database_Collation,Database_Name FROM #CollationComparison WHERE
Server_Collation <>
Database_Collation
SELECT DISTINCT * FROM #CollationComparison WHERE
Column_Collation <>
Database_Collation
--Now we can deal with Collation
Difference at query level, e.g;
--If we can change the database
schema, Change Collation of a column -
--Syntax
ALTER TABLE tablename
ALTER COLUMN columnname datatype
COLLATE collationname
--Example
ALTER TABLE MyTable
ALTER COLUMN Columnname
COLLATE Latin1_General_CS_AS
--Example: To Change Collation of a database
ALTER DATABASE WDDW COLLATE Latin1_General_CS_AS
--Then use below automated TSQL script
to change the COLLATE that is different to existing collation, the script as
follows:
declare @toCollation sysname
SET @toCollation = 'Latin1_General_CI_AS'
-- Database
default collate
SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +
' ALTER COLUMN '
+ COLUMN_NAME + ' ' + DATA_TYPE +
CASE WHEN CHARACTER_MAXIMUM_LENGTH =
-1 then '(max)'
WHEN DATA_TYPE in ('text','ntext') then ''
WHEN CHARACTER_MAXIMUM_LENGTH
IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
+' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END
FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES
ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
AND TABLE_TYPE = 'BASE TABLE'
and COLLATION_NAME <> @toCollation
To know more details about Collation, refer below KB
Comments
Post a Comment