Avoiding “Cannot resolve the collation conflict exception in SQL Server”

31 12 2015

Avoiding “Cannot resolve the collation conflict exception in SQL Server”

This exception is usually thrown when a comparison between character data (‘strings’) is done and both the source have different collation. This can occur with temporary as well as permanent tables.

There are various ways to fix this but it purely depends on how and what kind of tables\query you are writing.

  • While comparing complex #Temp tables you can specify “COLLATE DATABASE_DEFAULT” with character based column
    E.g
create table #TempTable1 (ID int, Name varchar(100)COLLATE DATABASE_DEFAULT)
  • While dumping data into #Temp and then comparing you may like to utilize “into” clause. This would create the #Temp table with the collation of the database in which TestTable1 resides.
    E.g.
select * into #TempTable1 from TestTable1
  • While comparing permanent tables we dodnot have much choice of changing the declaration of character column and so we may like to use “COLLATE DATABASE_DEFAULT” directly within the query in where caluse.
    E.g.
select t1.*
from TestDatabase2.dbo.TestTable1 t1
inner join TestTable1 t2 on t1.Name COLLATE DATABASE_DEFAULT = t2.Name COLLATE DATABASE_DEFAULT

Note:
The rule behind the creation of #Temp table is that it gets created in System Databases >> tempdp database. Now this tempdb database can have a different collation than the database where stored procedure\another table to compare resides. You can use any of the above pattern to avoid this situation

Advertisements

Actions

Information

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: