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