Application Security

Issue: Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS”

 

I received the following error when I was attempting to compare columns in a SQL query between two tables located in two separate databases. In this case, the collation settings between the two databases were configured differently. Rather make any changes to the databases, I was able to apply a simple fix to my SQL query:

ORIGINAL QUERY –

UPDATE

    [database1].[dbo].[table1]

SET

    [id] = (SELECT [d2t1].[id] FROM [database2].[dbo].[table1] [d2t1] WHERE [d2t1].[name] = [database1].[dbo].[table1].[name])

WHERE

    [id] IS NULL

FIX –

Simply apply the default collation to the fields you are comparing.

UPDATE

    [database1].[dbo].[table1]

SET

    [id] = (SELECT [d2t1].[id] FROM [database2].[dbo].[table1] [d2t1] WHERE [d2t1].[name] COLLATE DATABASE_DEFAULT = [database1].[dbo].[table1].[name] COLLATE DATABASE_DEFAULT)

WHERE

    [id] IS NULL

 

Find out more about SQL here:

  1. Float vs Decimal in SQL Server
  2. Calculate weekdays in SQL
  3. SQL server error: changes not permitted