Advertisements
Home > DBA Stuff, SQL DB Engine > SQL DBA – Collation Conflict in SQL Server

SQL DBA – Collation Conflict in SQL Server


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

Advertisements
  1. Taruna Makhija
    June 13, 2012 at 7:23 pm

    I was using the collate in where clause with the fields on the both sides , but the moment i used it with the right hand side field (ie only one side) on which i was joining my tables, it worked wonders.

    Thanks a lot , ur tip was really helpful.

  2. Taruna Makhija
    June 13, 2012 at 7:12 pm

    sorry, there was a typo error in my email id

  3. Taruna Makhija
    June 13, 2012 at 7:11 pm

    I am getting the following error which is same to the error you have mentioned but the tables i am joining are of same database-

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

    in the below query which is part of the procedure i am creating:::::::

    if(@ph”)
    if (exists(select * from TM_CDSADR_M) and exists(select * from TM_CDSPHN_M) and exists(select * from TM_CDSCTM_M))
    select top 1 @cn_p=ctm.CTM_NBR
    from TM_CDSCTM_M ctm join TM_CDSADR_M adr on(ctm.CTM_NBR=adr.CTM_NBR) join TM_CDSPHN_M phn on (ctm.CTM_NBR=phn.CTM_NBR)
    where UPPER(LTRIM(RTRIM(ATN_1ST))) =UPPER(LTRIM(RTRIM(@fname))) and UPPER(LTRIM(RTRIM(ATN_END))) =UPPER(LTRIM(RTRIM(@lname))) and LTRIM(RTRIM(adr.PHO_NBR)) =LTRIM(RTRIM(@ph))
    and PROMO=’Y’
    and REC_STA=’A’

    Though I have tried using COLLATE DATABASE_DEFAULT but again i am getting the same error,can u please help??

    I know no one can help better than you as I am working with the same company where you had been working for quite a year and i really appreciate your way of working and writing procedures.Infact everyone does appreciates you here.

  1. March 4, 2016 at 5:04 pm
  2. July 7, 2015 at 2:30 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: