I recently had to run a query for one of our developers where the script was inserting a number of rows. All went well in the US and JP business locations, but then ran into this error on the UK server: “Cannot resolve collation conflict for equal to operation”.
After searching a bit, I found this example that resolved the issue. Below is an example of the script that threw the error:
|
1 2 3 4 |
SELECT firstName, middleInitial, lastName FROM myPersons mp INNER JOIN myCompany mc WHERE mp.companyID = mc.companyID |
I resolved the error by adding the following to the script. Note the “COLLATE DATABASE_DEFAULT” and their locations:
|
1 2 3 4 |
SELECT firstName, middleInitial, lastName FROM myPersons mp INNER JOIN myCompany mc WHERE mp.companyID COLLATE DATABASE_DEFAULT = mc.companyID COLLATE DATABASE_DEFAULT |
For a MERGE statement, add it to the end of the ON statement:
|
1 2 3 4 5 6 7 8 9 10 11 |
MERGE [dbo].[context] AS Target USING #context AS Source ON Target.[contextName] = Source.[contextName] COLLATE DATABASE_DEFAULT WHEN MATCHED THEN UPDATE SET Target.[description] = Source.[description], Target.[applicationID] = Source.[applicationID], Target.[isControlledContext] = Source.[isControlledContext] WHEN NOT MATCHED BY Target THEN INSERT ([contextName], [description], [applicationID], [isControlledContext]) VALUES (Source.[contextName], Source.[description], Source.[applicationID], Source.[isControlledContext]); |
Views – 2329