Joseph Michael Pesch
VP Programming

SQL Server Collation Conflict

by 30. April 2010 16:17

You may receive an error like the one shown below when joining two tables that have different collation settings.  This can happen commonly when using temporary tables if the TEMPDB database has different collation settings than the operational database.  For example: I had an environment where the TEMPDB had "SQL_Latin1_General_CP1_CI_AS" while the operational database had "Latin1_General_CI_AI".  In my case I created a SQL statement using a temporary table and then joined the temporary table to a table in the operational database and received the following error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation. The resolution was to apply the COLLATE attribute to the joined column(s) as shown in sample below.

create table #tmp(DNIS char(10))

 

 

insert into #tmp values('0000000001')

insert into #tmp values('0000000002')

insert into #tmp values('0000000003')

insert into #tmp values('0000000004')

insert into #tmp values('0000000005')

 

 

select cd.DNIS

from CallDetails cd

join #tmp on cd.DNIS = #tmp.DNIS collate Latin1_General_CI_AI

 

 

Tags:

SQL Server

Comments are closed