web analytics

Cannot Resolve Collation Conflict

Collation can be defined at column level in MS SQL Server. An annoying error that comes up when using two columns that have different collations defined.

For example:

SELECT A.USER1, A.USER2, B.ROLENAME

FROM USERS A, ROLES B

WHERE A.ROLEID = B.ROLEID

A.ROLEID and B.ROLEID both have different collations and it upon executing above query it will generate an error: “cannot resolve collation conflict.

To resolve this error add COLLATE DATABASE_DEFAULT on both side of equal operator:

SELECT A.USER1, A.USER2, B.ROLENAME

FROM USERS A, ROLES B

WHERE A.ROLEID COLLATE DATABASE_DEFAULT = B.ROLEID COLLATE DATABASE_DEFAULT

Resolved.

Leave a Reply

%d bloggers like this: