March 4, 2010 at 12:48 pm
I have seen posts for this issue but I'm not exactly sure what the correct and or best method is to resolve.
I am joining data from 2 different datasources on 2 columns. In one datasource the column has a datatype of CHAR(3) and the other is CHAR(30). It is the provider column that is causing the error as I can remove that column from the sql statement and only join on the MRN with no errors. Apparently the collation in the databases is different and I receive the following error when I attempt to join the data:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation.
We can not change the collation in either database so that's not an available option and I don't really know why each database was set to it's collation since these are 3rd party systems.
So, is it possible to get around this issue of different collations and join the data? I am using CTE's and OPENQUERY to build each table and then I am joining the tables. (See code below which I have trimmed down for posting)
WITH table1 AS
(
SELECT * FROM OPENQUERY(linkedserver1,'
SELECT
id AS ''mrn''
key_value AS ''provider''
FROM
provider
')
),
table2 AS
(
SELECT * FROM OPENQUERY(linkedserver2,'
SELECT
a.account AS ''mrn''
,a.providercode AS ''provider''
FROM
charge a
')
)
SELECT
table1.provider
,table2.mrn
FROM
table1 INNER JOIN table2 ON table1.provider = table2.provider AND table1.mrn = table2.mrn
March 4, 2010 at 12:57 pm
If you can run something like this, perhaps you CAN find out the collation of the columns:
USE AdventureWorks
GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'Address')
AND name = 'City'
Then you can use COLLATE in your SELECT, see this link
March 4, 2010 at 1:22 pm
In table1 the collation for the provider column is: Latin1_General_BIN
In table2 the collation for the provider column is:SQL_Latin1_General_CP1_CI_AS
I updated the sql below and the statement executes successfully yet there are no results, and I know there should be matching results..?? What am I missing?
WITH table1 AS
(
SELECT * FROM OPENQUERY(linkedserver1,'
SELECT
id AS ''mrn''
key_value AS ''provider''
FROM
provider
')
),
table2 AS
(
SELECT * FROM OPENQUERY(linkedserver2,'
SELECT
a.account AS ''mrn''
,a.providercode AS ''provider''
FROM
charge a
')
)
SELECT
table1.provider
,table2.mrn
FROM
table1 INNER JOIN table2 ON table1.provider = table2.provider AND table1.mrn = table2.mrn COLLATE SQL_Latin1_General_CP1_CI_AS
March 4, 2010 at 2:08 pm
Changed to LEFT OUTER JOIN and the data I expect is showing up. My Bad! 😀
Thanks for the help!
March 4, 2010 at 2:13 pm
You are welcome! Glad it wasn't too hard.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply