Linked Server Collation Issue

Hi Mates,

Today I would like to share an issue that has been posted in of the groups. It's actually is a simple issue however I was not able to answer it properly.  Below is the message that has been shared 

Msg 468, Level 16, State 9, Line 5

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

I thought of reproducing the issue and here are the details

Instance 1 : Collation is Latin1_General_CI_AS

Instance 2: Collation is Latin1_General_CI_AS_KS_WS

I created Linked server on Instance1 that connects to Instance2. 

Below is the query what I ran to reproduce the issue on Instance1

select * from [instance2].[msdb].[dbo].[backupset]
where collation_name in (select collation_name from msdb..backupset )

My initial solution was it is not possible unless we use the keyword collate. But later I realized there lies an option in Linked server properties






























By default the remote collation property is  set to true once we change that to false we will no more have the issue



Comments