Heya people, gonna quickly run through how to adjust queries to ignore collation. I will return with another Mini Post to show how to actually change the collation of an instance and a DB.
You may have run into an scenario where your database has a different collation from that of your instance
(for example, the database could have been taken from an instance with a different collation than that of your current instance).
When you attempt to compare two columns, one with collation A and one with collation B, you will get the following error:
"Cannot resolve the collation conflict"
along with the names of collation A and B as well as the comparison operation that was attempted (whether it was '=', BETWEEN', 'IN' etc)
To resolve this, you can set the collation of A (the previously foreign DB) to be the collation of the current instance. In your WHERE clause where you are doing your comparisons place "COLLATE DATABASE_DEFAULT" immediately after the name of the column used for comparison like so:
...WHERE A.Col1 COLLATE DATABASE_DEFAULT in (SELECT B.Col1 FROM B)
No more error. :-)
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to
politely correct or comment as you see fit. Your feedback is always
welcomed. :-)
No comments:
Post a Comment