- 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.
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
Pingback from syngu.com
syngu.comSQL Server: Join tables from 2 databases,... | SQL and .NET | Syngu
Can this be applied when creating a view by any chance?
KrishMy query works, however when I use the same code to create a view i get the same error message!
Hi Krish, can you post the code here? A view is almost the same as a table, so the answer is yes.
capsoftSure, its:
KrishSELECT 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
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.
KrishHi - I've managed to get it to work!
KrishThanks for your reply!
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).
ScottyAlso, I just wasted 5 minutes watching your tag cloud follow my mouse move around..
Hi Scotty,
JP HellemonsThanks 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.
Pingback from com-lab.biz
com-lab.bizSQL Server query from two databases in asp.net | user92