MSSQL Collation Conflict

Ever get the following error?

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

I had separate databases on different servers that I needed to query off of. In my case, it was a join between a table on the local server and a table on a remote linked server in Microsoft SQL Server. This has to deal with the collation setting for the database when it was first created. In my case, the collation for the local database was set to Latin1_General_CI_AI, while the collation for the remote database was set to SQL_Latin1_General_CP1_CI_AS. The fix is easy, just specify COLLATE DATABASE_DEFAULT for any text comparisons within your SQL statement. Here’s a few examples:

SELECT loc_tbl.some_text
FROM db_name.dbo.local_table AS loc_tbl
  JOIN linked_server.db_name.dbo.remote_table AS rem_tbl
    ON loc_tbl.some_col = rem_tbl.some_col COLLATE DATABASE_DEFAULT

Here’s another way to do it:

SELECT loc_tbl.some_text
FROM db_name.dbo.local_table AS loc_tbl
  JOIN linked_server.db_name.dbo.remote_table AS rem_tbl ON loc_tbl.some_idx = rem_tbl.some_idx
WHERE
  rem_tbl.some_col COLLATE DATABASE_DEFAULT = loc_tbl.some_col