Doing a join across two databases with different collations on SQL Server and getting an error

Question

I know  I know with what I wrote in the question I shouldn t be surprised   But my situation is slowly working on an inherited POS system and my predecessor apparently wasn t aware of JOINs so when I looked into one of the internal pages that loads for 60 seconds I see that it s a fairly quick  rewrite these 8 queries as one query with JOINs situation   Problem is that besides not knowing about JOINs he also seems to have had a fetish for multiple databases and surprise  surprise they use different collations   Fact of the matter is we use all  normal  latin characters that English speaking people would consider the entire alphabet and this whole thing will be out of use in a few months so a bandaid is all I need   Long story short is I need some kind of method to cast to a single collation so I can compare two fields from two databases   Exact error is      Cannot resolve the collation conflict   between    SQL Latin1 General CP850 CI AI  and    SQL Latin1 General CP1 CI AS  in the   equal to operation

User · Accepted Answer

You can use the collate clause in a query  I can t find my example right now  so my syntax is probably wrong - I hope it points you in the right direction   select sone field collate SQL Latin1 General CP850 CI AI   from table 1     inner join table 2       on  table 1 field collate SQL Latin1 General CP850 CI AI   table 2 field    where whatever

User · Answer

A general purpose way is to coerce the collation to DATABASE DEFAULT  This removes hardcoding the collation name which could change   It s also useful for temp table and table variables  and where you may not know the server collation  eg you are a vendor placing your system on the customer s server   select     sone field collate DATABASE DEFAULT from     table 1     inner join     table 2 on table 1 field collate DATABASE DEFAULT   table 2 field where whatever

[sql-server] Doing a join across two databases with different collations on SQL Server and getting an error

Examples related to sql-server

Examples related to tsql

Examples related to collation