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

Cannot resolve the Collation Conflict.

Problem

We had created a new Database in SQL Server 2008 instance; we received the following error massage while using SQL Server Management Studio:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation. (Microsoft SQL Server, Error:468)

Additionally, when we tried to run a query joining two tables, one table that belongs to the database created and the other a temporary table, both tables joined by a common column of varchar(10) data type, then we received the following error from SSMS:

(1 row(s) affected)
Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.



Cause.

We configured the SQL Server instance with the SQL_Latin1_General_CP1_CI_AS because it is the standard in our organization, and that means all system databases on the server have that collation (including TempDB), but we had created the new database with a different collation, collation Latin1_General_CI_AS. The conflict is originated by the difference in collation between the instance and the created database.

Solution.

If possible change the database collation. The following link gives instructions on how to change the database collation.

http://msdn.microsoft.com/en-us/library/ms175835.aspx

To change the server collation, you either have to reinstall SQL Server or rebuild system databases. For more information, please see the following article:

http://msdn.microsoft.com/en-us/library/ms179254.aspx

To allow specific queries to run despite the difference on collations, we need to modify those queries and include the COLLATE or COLLATE database_default clause when comparing string columns with different collations. Please see the following article, for more information:

http://msdn.microsoft.com/en-us/library/ms184391.aspx