1

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?

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

3 Answers 3

2

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:

 SELECT *
 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.

2

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.

SELECT
  *
FROM
 dbo.ThisTable t2
 join
 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
  • +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, 2009 at 14:17
0

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

You must log in to answer this question.