Cannot resolve the collation conflict between 'SQL_Latin1_General_CP1_CI_AS' and 'Latin1_General_CI_AI' in the like operation.
I'm working on a Sunday trying to finish something up and I run into this error. Urg. :) First, let me give you a brief summary of collation. Basically it's the encoding that a database uses to store text. This can allow (or not allow) for the storing of different character sets (e.g. think about foreign language characters, etc.).
Today, I was finishing a stored procedure that inserted records into a table variable with a nested select statement. Now, here's where the problem comes in, the nested select at some point used either the ‘master' database or the ‘tempdb' (likely the tempdb). The collation between the database I'm using and the tempdb is different which caused this problem. There maybe a good reason the collations are different, I won't go into those but I will offer the on the fly solution I found to get around this:
SELECT username COLLATE Latin1_General_CI_AI FROM users
As you can see, it's changing the collation on the fly. My query was longer and nested, this is just my simple example. After changing this, everything ran smoothly and returned the correct results. Seeing this error maybe a good talking about why all of your databases aren't using the same collation which would simplify things. Like I said, there maybe a good reason why they're different (say for example you're using a vended database that has a different collation than some in house databases that have been designed with specific needs). Anyway, hope this helps someone!