Common Collation Conflict

Whenever you access database schema outside the scope of the current database there is a chance that you will run into a collation conflict error. Most of the instances of this error that I have encountered have been caused by remote databases using the older SQL collation (SQL_Latin1_General_CP1_CI_AS) instead of the windows collation (Latin1_General_CI_AS).

Because these two collations use the Latin1 (ANSI) code page and have the same sort orders (CI_AS), there is no harm in changing from one to the other in a conflict scenario.

Consider the following query:

SELECT cur.[Column1]
FROM [dbo].[Table1] AS cur
INNER JOIN [RemoteServer].[RemoteDb].[dbo].[Table1] AS rem
ON (rem.[Column1] = cur.[Column1]);

This produces the following error because the remote table column has been defined using the SQL collation.

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

To resolve this problem, we could simply add the COLLATE statement to convert to the local Windows collation.

SELECT cur.[Column1]
FROM [dbo].[Table1] AS cur
INNER JOIN [RemoteServer].[RemoteDb].[dbo].[Table1] AS rem
ON (rem.[Column1] COLLATE Latin1_General_CI_AS = cur.[Column1]);

However, there is another way; a linked server can be configured to ignore remote collations by changing the "Use Remote Collation" Server Option to FALSE. This is a far cleaner, simpler way to handle collation conflicts where linked servers are involved.