Wednesday, October 1, 2008

ERROR: Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS_KS_WS in the equal to operation

If you get the following SQL Server error message:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS_KS_WS in the equal to operation.


It is because of Collation settings on two columns you are joining on (or maybe something similar. There are permanent fixes, but are not simple and mostly assume you can change table definitions, etc.

I prefer the simple fix.  

The first step is to figure out what are two collations that are conflicting. Assuming that the collation has not been set at the column level and is instead at the database level, here s what you need to do.

Execute the following two statements (after you put your two database names in the statements).

select DATABASEPROPERTYEX ( 'DB1', N'Collation' )
select DATABASEPROPERTYEX ( 'DB2', N'Collation' )

If you are on SharePoint you will likely get Latin1_General_CI_AS_KS_WS. If you are on most any other database and use the default settigs you may get: SQL_Latin1_General_CP1_CI_AS.

Now that you know what you are dealing with you just need to do something similar to a CAST, but for Collation. It is called Collate. Here is an example of a query that joins on two columns that have different collations.

select * from DB1.dbo.User u
join DB2.dbo.UserMap m
on (u.email = m.email COLLATE SQL_Latin1_General_CP1_CI_AS)


For your query you will should choose the Collation of one of the columns.

If you want to understand more about this, I suggest checking this link:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/12/883.aspx

The database schema is too old to perform this operation in this SharePoint cluster.

Have you ever received the following error while trying to reconnect or restore a SQL Server database in SharePoint? The database schema is too old to perform this operation in this SharePoint cluster. This is a little mis-leading in my opinion. It is talking about a version mismatch between SharePoint Configuration database and the database you are trying to restore or reconnect to SharePoint. This should apply to both Windows SharePoint Services v2 (WSS) and SharePoint Portal Server 2003 (SPS), though the names of the databases may be different. Let's assume your content database is called xxx_SITE where xxx is the name of your SharePoint site if you are using SPS. If you are using WSS you are looking for your Content database. If you open up SQL Server Management Studio 2005 or Query Analyzer you can run the following two queries. You will see that there is a mismatch on the version numbers. select Version from xxx_SITE.dbo.SystemVersion select SchemaVersion from SPS01_Config_db.dbo.Globals What this means is that you have a different version of SharePoint installed than the version that was installed when SharePoint was backed up. I have found on the web documentation that says it has to be the same service pack level, but my experiences has told me that the patches have the be the same as well. Here if you look under the comments for one by Andrew Woodward you'll see he also had the same experience. This site seems to have a good list of what the different versions are in relation to patches. Once you have the same version of SharePoint installed again everything should restore fine. If you are not sure what the previous version was you can get it from the content database (xxx_SITE in this example) using the above query.