I have a SQL query that compares a value in the database to a constant:
SELECT * FROM my_table
INNER JOIN #TempTable tem
ON my_table.id = temp.id
AND my_table.key = 'SOME STRING'
And I get the error:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
How can I get around this? (without making changes to the database)
UPDATE: I get this error even if I remove the last like (the string comparison)...
Database collation To apply new collation to existing tables of a database, the user can set new collation on TEXT columns of tables.
DROP TABLE IF EXISTS #TempTable; CREATE TABLE #TempTable ( ProductCode VARCHAR(5) COLLATE DATABASE_DEFAULT NOT NULL, ProductDesc VARCHAR(200) NULL ); INSERT INTO #TempTable ( ProductCode,ProductDesc) SELECT ProductCode,ProductDesc FROM dbo. TestData; SELECT t. ProductCode, tt. ProductDesc FROM dbo.
The SQL_Latin1_General_CP1_CI_AS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1_General_CI_AS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.
If you then specify a COLLATE clause in the query that is different than the collation used for the index, you will have a performance penalty because you won't be using that index.
Seems your id
's are VARCHAR
s with different collations.
Try this:
SELECT *
FROM my_table
INNER JOIN
#TempTable tem
ON my_table.id = temp.id COLLATE SQL_Latin1_General_CP1_CI_AS
AND my_table.key = 'SOME STRING'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With