I've got a SQL Server 2005 database that has been built using a different collation from the one one our servers. I'm getting this error whenever I try and run the site:

Exception message: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation

Does anyone know of anyway I can fix the error without having to rebuild the database?

  • Do you have a query which joins tables from two databases on that server? – splattne May 22 '09 at 10:55
  • Should this be stackoverflow because it's coding? – gbn May 31 '09 at 13:10

If the problem is a query and you have not the option to rebuild that database, you could go with the COLLATE keyword, for example:

 FROM OtherServer.xyz.dbo.Table2 t2
     inner join myTable t1 
         on t1.name = t2.name COLLATE SQL_Latin1_General_CP1_CI_AS

Here is the MSDN documentation for COLLATE.


Use COLLATE Database_Default to coerce the join or compare.

This has the advanatge of being neutral and does not hard code the collation, but has the disadvantage of unexpected results if you expect binary or case sensitive sorting. In this case, I'd expect it to be OK.

 dbo.ThisTable t2
 dbo.ThatTable t1 on t1.name = t2.name COLLATE Database_Default
--or dbo.myTable t1 on t1.name COLLATE Database_Default = t2.name COLLATE Database_Default
--or dbo.myTable t1 on t1.name COLLATE Database_Default = t2.name
  • +1. You do NOT have to rebuild anything. Just include the coersion statements, and the query compiler will solve the problem for you. – p.campbell May 31 '09 at 14:17

The reccomended way to resolve this is to either rebuild the DB or re-install the SQL instance, which I guess is not what you wanted to here.

There is an application here that claims to be able to do this for you, its called SQL 2000 coalition changer, but claims to work with SQL 2005 as well. I've not used it, so I cannot give you an experience with it, but its an option. As always, make sure your fully backed up first

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy