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