Collations in SQL Server

Error Message:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Problem arises because your SQL Query may be referencing (or conducting operations with) 2 databases or tables that are collated differently.
To check, run the following queries:

SELECT cast( DATABASEPROPERTYEX ( db_name(), N'Collation' ) as varchar(128) )

SELECT cast( DATABASEPROPERTYEX ( 'nameofotherdb', N'Collation' ) as varchar(128) )

The 2 values returned reflect the collation property of the databases respectively.

Solution:
1) Create a new database (and set the COLLATE property correctly).
2) Alter the database to reflect the correct COLLATE property.
3) In the event that your database need to reference databases with different COLLATION properties, furnish your SQL queries to handle the collation conflict:

select *
from dbo.registrations r
inner join dbo.staffProfile s
on s.name = r.fullname COLLATE SQL_Latin1_General_CP1_CI_A

Reference: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07.aspx