I have changed the collation of test database using the follwoing query:
USE master; GO ALTER DATABASE test COLLATE SQL_Latin1_General_CP1_CI_AI ; GO --Verify the collation setting. SELECT name, collation_name FROM sys.databases WHERE name = N'test'; GO
Now when I want to compare a columns of test database against test2 database, I receive the following error:
Cannot resolve the collation conflict between
“SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in
the equal to operation.
I have checked that both databases has
SQL_Latin1_General_CP1_CI_AI collation in :
Management studio > Database > properties > options > collation.
Is there another option rather than the database itself (e.g. schema) which still holds the old collation?
First: Collations for columns are stored per-each column.
ALTER DATABASE ... COLLATE ... statement changes only the default collation of the database itself. It does not change the collation of any existing columns within the database. This change affects:
- database-level meta-data (mostly names of database-level objects: tables, columns, procedures, functions, triggers, indexes, users, views, constraints, etc, etc) except in Contained databases (where the DB-level meta-data is always
- new string columns that do not specify a
COLLATEclause. New via:
CREATE TABLE ...
ALTER TABLE ... ADD
DECLARE @TableVariable TABLE ...(these default to the database’s collation, not
- altered string columns that do not specify a
ALTER TABLE ... ALTER COLUMN ...)
- new or altered columns that do specify
- string literals and variables
For an in-depth look at changing database-level (and even instance-level) collations, please see my post:
For more info on working with collations in general, please visit: Collations Info
Check the collation on the columns you are comparing, you’ll probably find out that on
test the columns are still in SQL_Latin1_General_CP1_CI_AS. Try declaring collation explicitly in your comparison and see if it helps.
Also, are you storing something in tempdb? If your instance is based on one or the other you could have this issue.