9 Comments
  • Posted in:
  • SQL

SQL-Server-Management-Studio-2008

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.

kick it on DotNetKicks.com Shout it

Pin on pinterest Plus on Googleplus Post on LinkedIn

Comments

Comment by syngu.com

Pingback from syngu.com

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

Comment by Krish

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
Comment by capsoft

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

capsoft
Comment by Krish

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
Comment by Krish

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
Comment by Krish

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

Thanks for your reply!

Krish
Comment by Scotty

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
Comment by JP Hellemons

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
Comment by com-lab.biz

Pingback from com-lab.biz

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