CODEWITHSUNDEEP
sql-server
user4612290
user4612290

Reputation: 321

"Cannot resolve collation conflict" even after fixing the collation

The current database I'm using "PrimaryDatabase" has the collation "SQL_Latin1_General_CP1_CI_AS", while the "SecondaryDatabase" I'm trying to access has the collation "Arabic_CI_AS"

I changed the collation for the SecondaryDatabase and set it to " SQL_Latin1_General_CP1_CI_AS" and made sure it has been changed as well as in its tables.

However, when i run the query below I still get collation conflict.

select * from [MYSERVER].[SecondaryDatabase].[dbo].[SecondaryTableName] 
where ltrim(rtrim([SecondaryTablename])) not in (select ltrim(rtrim(PrimaryFieldname))  from PrimaryTablename where PrimaryFieldName2=1)

Upvotes: 0

Views: 3346

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

One way to make your query work is to use COLLATE clause in order to apply a collation cast on both fields being involved in the predicate of the WHERE clause:

select * 
from [MYSERVER].[SecondaryDatabase].[dbo].[SecondaryTableName] 
where ltrim(rtrim([SecondaryFieldname])) COLLATE SQL_Latin1_General_CP1_CI_AS
      not in (select ltrim(rtrim(PrimaryFieldname)) COLLATE SQL_Latin1_General_CP1_CI_AS 
              from PrimaryTablename 
              where PrimaryFieldName2 = 1)

The COLLATE clause applied to PrimaryFieldname might not be necessary, since this is the default collation of the corresponding database (so probably PrimaryFieldname already has this collation).

Another solution is to change the collation at field level, e.g.:

ALTER TABLE SecondaryDatabase
ALTER COLUMN SecondaryFieldname VARCHAR(50)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL

Upvotes: 2

Related Questions