Connecting to two databases with different mappings on SQL Server and getting an error

Question

I know, I know what I wrote in the question, I should not be surprised. But my situation is slowly working on an inherited POS system, and my predecessor did not seem to know about JOINs, so when I looked at one of the internal pages loading in 60 seconds, I see that it is pretty fast, rewrite these 8 queries as a single query with a JOIN situation. The problem is that, in addition to not knowing about JOINs, he also seems to have had a fetish for many databases and was surprised to be surprised that they use different mappings. The fact is that we use all the "normal" Latin characters that English-speaking people would consider the whole alphabet, and all this will be useless in a few months, so I need a bandida.

In short, I need some method to bring to one mapping, so I can compare two fields from two databases.

Exact error:

Cannot resolve collision conflict between "SQL_Latin1_General_CP850_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in equal operation.

Answer 1

You can use the collate clause in the query (I cannot find my example right now, so my syntax is probably incorrect - 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 

Answer 2

A universal way is to force matching with DATABASE_DEFAULT. This removes the hardcoding sort name, which may change.

It is also useful for temp table variables and tables, and where you cannot know the server settings (for example, you are the provider hosting your system on a client 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