CODEWITHSUNDEEP
sqlsql-server
Keith Costa
Keith Costa

Reputation: 1793

collation conflict sql server

When i issue a SQL query, sometimes I get the following error message:

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

I often solve this problem just make both table collation same. so i need to know is there any quick way to fix this issue.

I'd like to put something special in my SQL query as a result if collation is not same for both table in sql query then also query will work perfectly without any error. is there any solution?

Upvotes: 10

Views: 23830

Answers (3)

sandeep
sandeep

Reputation: 17

There could be a possibility that both the servers use different Collations. If yes then you would get an error similar to then one I mentioned at the top of this topic. What you should do in this case?

  1. You can alter the default Collation of either of the table columns/fields, but this could have a ripple effect for other tables currently in use with the altered table.

  2. Use COLLATE DATABASE_DEFAULT keyword while matching the columns/fields

like:

SELECT T1.EmployeeName, T2.DeptName
FROM ServerA.dbo.EmpTab T1
JOIN ServerB.dbo.DeptTab T2
ON T1.DeptCode COLLATE DATABASE_DEFAULT
= T2.DeptCode COLLATE DATABASE_DEFAULT

Upvotes: 0

Stephen Turner
Stephen Turner

Reputation: 7314

You can force which collation by using the COLLATE clause.

i.e.

SELECT *
FROM Table1 T1
INNER JOIN Server2.dbo.Table2 T2
ON T1.Name = T2.Name COLLATE database_default

Collation conflicts are common when joining tables between two databases or servers, especially if the version of the DB is different.

Upvotes: 16

Andomar
Andomar

Reputation: 238086

You can specify a collation in a query using the collate clause:

where  col1 = col2 collate Latin1_General_CI_AS

Upvotes: 2

Related Questions