Home > Alter, Collations, SQL Queries, SQL Server, T-SQL > resolving collation issues in sql server

resolving collation issues in sql server

I had to write a query to fetch data from tables located in two different databases using a join. The collation used for the databases were different. This resulted in an error as below

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “SQL_Hungarian_CP1250_CI_AS” in the equal to operation
.

This was a new issue that I faced, May be because i never queried the databases on different collations :).

How ever this is easy to resolve. We can use the “COLLATE DATABASE_DEFAULT” in the join clause for the columns used from the second database. This will change the collation of the column to the default collation used in the database on which the query gets executed. An example is shown below


SELECT *
FROM
ARCOBJECTS ARO
INNER JOIN
TESTDB.DBO.SUBLIST SUB
ON
SUB.SUBNAME COLLATE DATABASE_DEFAULT= ARO.DRIVERTABLE

You can alternatively mention the collation that you want to use with “COLLATE <<collation Name>>

example:

SELECT *
FROM
ARCOBJECTS ARO
INNER JOIN
TESTDB.DBO.SUBLIST SUB
ON
SUB.SUBNAME COLLATE SQL_Latin1_General_CP1_CI_AS = ARO.DRIVERTABLE

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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: