SQL Server: Join tables from 2 databases, collation issue

January 10. 2011 9 Comments

Posted in:
SQL

If you want to inner join two tables from different databases, you might encounter some collation errors.

When I executed this query:

SELECT ProductCode, ProductResalePrice
  FROM [database1].[dbo].[PRODUCTS] 
  inner join [database2].[dbo].[items] on
  ProductCode = [items].itemCode
where ProductDeleted = 0

I got this message:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

So when I executed the following query, I found out that I had two different Collations:

SELECT DATABASEPROPERTYEX('database1', 'Collation') SQLCollation;
SELECT DATABASEPROPERTYEX('database2', 'Collation') SQLCollation;

I thought that it was because one database was migrated from SQL Server 2000 to 2005 to 2008 and the other one (database2) was from 2005 to 2008. But apparently the default collation depends on the region settings of your Windows installation and gets set when you install SQL Server.

Now you can change the collation of your database with various solutions which can be found around the internet, but I wanted to simply join two tables and do one update.

I had to Google a lot to find out, how to define collations when using an inner join. But here is the solution for my query:

SELECT ProductCode, ProductResalePrice
  FROM [database1].[dbo].[PRODUCTS] 
  inner join [database2].[dbo].[items] on
  ProductCode collate SQL_Latin1_General_CP1_CI_AS = [items].itemCode
where ProductDeleted = 0

So you can define collation on the fields which you join. Open-mouthed smile

By the way: this is a great resource for the different collations: http://msdn.microsoft.com/en-us/library/ms143515%28v=SQL.90%29.aspx

Hope that it helped you, it would have saved me a lot of frustrations and time.

Comments

July 13. 2011 13:19

Pingback from syngu.com

SQL Server: Join tables from 2 databases,... | SQL and .NET | Syngu

syngu.com

August 17. 2011 13:15

Can this be applied when creating a view by any chance?

My query works, however when I use the same code to create a view i get the same error message!

Krish

August 17. 2011 13:40

Hi Krish, can you post the code here? A view is almost the same as a table, so the answer is yes.

capsoft

August 17. 2011 14:45

Sure, its:

SELECT USERNAME, E.ENTITYID FROM EMPDETAILS ED
INNER JOIN ECONTRACT EC ON ED.EMPID=EC.EMPID
INNER JOIN ENTITY E ON ED.ENTITYID=E.ENTITYID
inner join GOKAN.WORKFLOW.dbo.Assignment on username collate SQL_Latin1_General_CP1_CI_AS = assignment.Ausername
WHERE
USERNAME IS NOT NULL AND
USERNAME <> '' AND
EROLENAME='ADMIN'
GROUP BY USERNAME, E.ENTITYID
ORDER BY USERNAME

Krish

August 17. 2011 14:48

It appears to be getting rid of the '...collate SQL_Latin1_General_CP1_CI_AS...' part of the query when saving the view. I'm using SQL server 2008.

Krish

August 17. 2011 14:58

Hi - I've managed to get it to work!

Thanks for your reply!

Krish

October 23. 2012 12:59

For reference: Krish's problem is that the visual designer for views in SSMS removes the collation when saving, therefore generating the error (nice one ms). The work around is to define the view using a script (CREATE VIEW/ALTER VIEW) and not the dodgy designer. (This is the case in SSMS 2008 at least).

Also, I just wasted 5 minutes watching your tag cloud follow my mouse move around..

Scotty

January 9. 2013 12:41

Hi Scotty,

Thanks for explaining how to solve Krish's problem! Really annoying that Sql server management studio has this bug!
And sorry for the tag cloud. it was a wordpress widget before. Some one converted it to html5 for blogengine. tagcumulus or something. Found www.ugochirico.com/.../...d-for-BlogEngineNET.aspx but switched to the html5 thing to don't have to depend on flash plugins. But I cannot find the url from the html5 version.

JP Hellemons

August 6. 2013 02:51

Pingback from com-lab.biz

SQL Server query from two databases in asp.net | user92

com-lab.biz