PROBLEM WITH COLLATION on SQLSERVER?

try the following T-SQL codes to verify or alter the database,table or field collation:

check DATABASE collation:

select databasepropertyex(db_name(),'collation') as collation_name

alter DATABASE collation

alter database MYDB collate Latin1_General_CI_AS

check TABLE collation:

select column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'MYTABLE'

check COLUMN collation:

select column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'MYTABLE'

alter COLUMN collation:

alter table MYTABLE
alter column MYCOLUMN value varchar(20)
COLLATE Latin1_General_CI_AS

Following a script for checking and examinate all DB COLUMNS:
(NOTE: this script wont modify anything. It will just create code lines for you)

declare @toCollation sysname

set @toCollation = 'Latin1_General_CI_AS'
set @toCollation = select databasepropertyex(db_name(),'collation') as collation_name

print 'TO COLLATION --> ' + @toCollation

SELECT 'ALTER TABLE '+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 +' -- was '+COLLATION_NAME
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

---

NOTE 1
Altering a database collation doesn't alter his tables too...
only new tables/fields will be affected, so you'll need to check and alter your current structure manually.
Use the above script.

---

NOTE 2
if you have views, stored procs, or applications already working with the current collation, BE VERY CAREFULL on altering any column COLLATION.
Altering collations may cause errors like COLLATION CONFLICT !
eg:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CS_AS" in the equal to operation.

if this happends, rollback your ALTERs.
This may be a message coming from a view or a stored...


Suggestion: never (i mean NEVER) alter a collation on a column if not necessary!!