SQL Server ; working around collation mismatches

Question

Imagine the following scenario: I have two servers which are on two separate private networks.

A linked server has been created, and there is a table in server A which has an updated version of the same table on server B. The updates are not extensive, essentially just a reassignment of some Boolean values.

The connection between the two servers is reliable but relatively slow, and there is a collation mismatch between the two tables.

Is there a recommended or standard way of updating information between two servers with a collation mismatch?

In linked server property, you can set use remote collation to TRUE. at tsql level, you can use collate. — Kin Shah, Apr 28 '15 at 12:18
Additionally, I would like to point out Linked Servers and Collation Compatibility from CSS and this answer to understand collation a bit more — Kin Shah, Apr 28 '15 at 15:43

RLF · Accepted Answer · 2015-04-28T20:09:14.403

5

You use the collate operator in the join. Such as:

select <column list>
from dbo.firsttable f
join linkedserver.database.dbo.secondtable d 
   on f.name collate database_default = d.name collate database_default

Of course, you can also specify a specific collation, such as: SQL_Latin1_General_CP1_CI_AI

edited Apr 28 '15 at 20:09

answered Apr 28 '15 at 12:23

RLF

14,015
2
33
47

I never have had to do both sides for collate database_default in the past, but this works as well. Accepting your edit. – RLF Apr 28 '15 at 20:17
Reminded me of a problem during my Jr. DBA days. :) – Ali Razeghi - AWS Apr 28 '15 at 20:54

SQL Server ; working around collation mismatches

1 Answers1