SQL with Manoj

Cannot resolve the collation conflict between “SQL_Latin1_General_CP437_BIN” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Just few days back I came across this error when I tried to join two tables from 2 different databases. Didn’t get any clue for a few minutes so I googled up this error (Thanks google baba).

Collation is MS SQL Server is used for specifying the ordering of characters when you create or alter a table or create a domain. Collation settings, which include character set, sort order, and other locale-specific settings, are fundamental to the structure and function of Microsoft SQL Server databases. SQL Server uses them implicitly in SQL statements. To use them explicitly you need to override the default collation or the collation you specified when creating or altering the table or creating the domain. Collation can be applied for char, varchar, text, nchar, nvarchar, and ntext data types.

For example:

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

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

More Info:
Collation Types: http://msdn.microsoft.com/en-us/library/ms144250%28SQL.90%29.aspx
Alter Table: http://msdn.microsoft.com/en-us/library/aa275462%28SQL.80%29.aspx
Alter Database: http://msdn.microsoft.com/en-us/library/aa275464%28SQL.80%29.aspx
Other: http://developer.mimer.com/documentation/html_92/Mimer_SQL_Engine_DocSet/Collations3.html