sql-server-2008debuggingselectcollationdbo

running query against two DB tables between linked servers causing collation error

Cannot resolve the collation conflict between
"SQL_Icelandic_Pref_CP1_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in 
the equal to operation.

I am trying to check for duplicates between two tables and created a linked server and then created the following query but I keep getting the following error and was wondering if there is a better way to do this or how to fix this error:

  select A.siteid
  from siteids A, DBSERVER02.PostFeeds.dbo.siteids B
  where A.siteid = B.siteid

I was also told that this query will take a long time to run given that I have over 100 millions of records in both tables.

Solution

  • Try using "collate" clause after one of the fields. If you don't know which is which, just type:

    where A.siteid collate database_default = B.siteid collate database_default