Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql-server

I have the following code

SELECT tA.FieldName As [Field Name],        COALESCE(tO_A.[desc], tO_B.[desc], tO_C.Name, tA.OldVAlue) AS [Old Value],        COALESCE(tN_A.[desc], tN_B.[desc], tN_C.Name, tA.NewValue) AS [New Value],        U.UserName AS [User Name],        CONVERT(varchar, tA.ChangeDate) AS [Change Date]    FROM D tA        JOIN         [DRTS].[dbo].[User] U           ON tA.UserID = U.UserID        LEFT JOIN         A tO_A           on tA.FieldName = 'AID'          AND tA.oldValue = CONVERT(VARCHAR, tO_A.ID)        LEFT JOIN         A tN_A           on tA.FieldName = 'AID'          AND tA.newValue = CONVERT(VARCHAR, tN_A.ID)        LEFT JOIN         B tO_B           on tA.FieldName = 'BID'          AND tA.oldValue = CONVERT(VARCHAR, tO_B.ID)        LEFT JOIN         B tN_B           on tA.FieldName = 'BID'          AND tA.newValue = CONVERT(VARCHAR, tN_B.ID)        LEFT JOIN         C tO_C           on tA.FieldName = 'CID'          AND tA.oldValue = tO_C.Name        LEFT JOIN         C tN_C           on tA.FieldName = 'CID'          AND tA.newValue = tN_C.Name  WHERE U.Fullname = @SearchTerm ORDER BY tA.ChangeDate 

When running the code I am getting the error pasted in the title after adding the two joins for table C. I think this may have something to do with the fact I'm using SQL Server 2008 and have restored a copy of this db on to my machine which is 2005.

like image 739
jhowe Avatar asked Oct 22 '09 14:10

jhowe


People also ask

Is SQL_Latin1_General_CP1_CI_AS the same as Latin1_General_CI_AS?

The SQL_Latin1_General_CP1_CI_AS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1_General_CI_AS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.

What is SQL collation SQL_Latin1_General_CP1_CI_AS?

The collate clause is used for case sensitive and case insensitive searches in the columns of the SQL server. There are two types of collate clause present: SQL_Latin1_General_CP1_CS_AS for case sensitive. SQL_Latin1_General_CP1_CI_AS for case insensitive.

How do I change the collation of an existing database?

Set or change the database collation using SSMS Alternatively, if the database already exists, right-click the database that you want and select Properties. Select the Options page, and select a collation from the Collation drop-down list. After you are finished, select OK.


2 Answers

I do the following:

...WHERE      fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT 

Works every time. :)

like image 132
Valkyrie Avatar answered Oct 14 '22 16:10

Valkyrie


You have a mismatch of two different collations in your table. You can check what collations each column in your table(s) has by using this query:

SELECT     col.name, col.collation_name FROM      sys.columns col WHERE     object_id = OBJECT_ID('YourTableName') 

Collations are needed and used when ordering and comparing strings. It's generally a good idea to have a single, unique collation used throughout your database - don't use different collations within a single table or database - you're only asking for trouble....

Once you've settled for one single collation, you can change those tables / columns that don't match yet using this command:

ALTER TABLE YourTableName   ALTER COLUMN OffendingColumn     VARCHAR(100) COLLATE Latin1_General_CI_AS NOT NULL 

Marc

UPDATE: to find the fulltext indices in your database, use this query here:

SELECT     fti.object_Id,     OBJECT_NAME(fti.object_id) 'Fulltext index',     fti.is_enabled,     i.name 'Index name',     OBJECT_NAME(i.object_id) 'Table name' FROM      sys.fulltext_indexes fti INNER JOIN      sys.indexes i ON fti.unique_index_id = i.index_id 

You can then drop the fulltext index using:

DROP FULLTEXT INDEX ON (tablename) 
like image 24
marc_s Avatar answered Oct 14 '22 15:10

marc_s