SQL Server Cannot resolve collation conflict for equal to operation

Few days ago I had copied some tables, Stored Procedures, Views etc  from one database to a new database. 

Now whenever I execute a Stored procedure, I got below error:

"Cannot resolve collation conflict for equal to operation."

Thre error clearly indicates that the collation types for the columns being joined in the sql statement is different.

I had overcome this error by telling the columns to use the databases default collation on either side of the join fixed the problem - e.g.

SELECT table1.*, table2.* FROM table1
INNER JOIN table2 ON table1.column1 COLLATE DATABASE_DEFAULT = table2.column1 COLLATE DATABASE_DEFAULT

SQL Server Cannot resolve collation conflict for equal to operation.

Today I migrated an exisiting set of tables from one database, to a new database, to isolate their work.


After copying all the stored procs, views, tables and data, one of my stored procs wouldn't run.


Everytime I tried to execute it, I got the error:


"Cannot resolve collation conflict for equal to operation."


It transpires that this error can be caused by many different problems, butt he main one being that the collation types for the columns being joined in the sql statement is different.


In my instance this was not the case, but by telling the columns to use the databases default collation on either side of the join fixed the problem - e.g.


SELECT table1.*, table2.* FROM table1
INNER JOIN table2 ON table1.column1 COLLATE DATABASE_DEFAULT = table2.column1 COLLATE DATABASE_DEFAULT


Ref: http://wyerarch.blogspot.in/search?updated-max=2009-06-29T01:12:00-07:00&max-results=7&start=14&by-date=false