Join Fails between two varchar(22) fields with the same collation.

I am trying to link views  through an inner join both  fields both are varchar(22) but the join only works on two records.  Yet when I use the value from o the field from one view to search in the other it finds it, no problem.  I checked the collation in both tables for the join fields and they are both the same" "SQL_Latin1_General_CP1_CI_AS"  .  One of the tables is brought over as a view through openv query from mysql.

here is the code fro the open query:

SELECT     leg_id, CONVERT(varchar(10), leg_date) AS Leg_Date, LTRIM(RTRIM(CONVERT(varchar(22), Link))) AS Link, LEFT(CONVERT(varchar(10), Sorian_Num), 7) 
                      AS Sorian
FROM         OPENQUERY(tsr, 'select * from `xxxxxxx`.`TS_Link2_Charts_Sorian`') AS TS_Notes

Open in new window


here is the actual join:
SELECT     dbo.MICU_Times_Analysis.PRID, dbo.MICU_Times_Analysis.Date_Line, dbo.MICU_Times_Analysis.Link, dbo.Sorian_Confirmed.Link AS Expr1
FROM         dbo.MICU_Times_Analysis
JOIN           dbo.Sorian_Confirmed ON dbo.MICU_Times_Analysis.Link = dbo.Sorian_Confirmed.Link
WHERE     (dbo.MICU_Times_Analysis.AtDest >= '3/1/2018')

Open in new window


I am stumped
LVL 5
WillOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>but the join only works on two records.
So .. this would imply that the JOIN works, but somehow the values are difference such that they are not joining.  

<wild guess>
Any leading or trailing spaces that can be removed via LTRIM(RTRIM()) ?
0
WillOwnerAuthor Commented:
Thanks for the reply Jim: Actually tried ltrim(trim on both sides didn't work.
0
WillOwnerAuthor Commented:
Here is the real kicker a vlookup in excel is a 100% match.  I am dumbfounded!
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ste5anSenior DeveloperCommented:
First of all: Use unambiguous date literals. E.g. '20180301'.
Then use table alias names to increase readability.

Did you look at your data?

SELECT TOP ( 1000 ) A.PRID ,
                    A.Date_Line ,
                    A.Link AS ALink ,
                    C.Link AS CLink
FROM   dbo.MICU_Times_Analysis A
       FULL JOIN dbo.Sorian_Confirmed C ON A.Link = C.Link
WHERE  A.AtDest >= '20180301';

Open in new window


and

SELECT TOP ( 1000 ) A.Link AS ALink ,
                    C.Link AS CLink
FROM   dbo.MICU_Times_Analysis A
       FULL JOIN dbo.Sorian_Confirmed C ON A.Link = C.Link;

Open in new window

0
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
What is the collation of the tempdb on the server where the query with the JOIN is executed?

When you bring over data via OPENQUERY in the view, I suspect it is using tempdb as a temporary storage which would cause problems if it is on a different collation. To confirm, you can actually specify a collation explicitly in the JOIN

SELECT dbo.MICU_Times_Analysis.PRID, dbo.MICU_Times_Analysis.Date_Line, dbo.MICU_Times_Analysis.Link, dbo.Sorian_Confirmed.Link AS Expr1
FROM   dbo.MICU_Times_Analysis
JOIN   dbo.Sorian_Confirmed ON dbo.MICU_Times_Analysis.Link COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Sorian_Confirmed.Link COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE  (dbo.MICU_Times_Analysis.AtDest >= '3/1/2018')

Open in new window


If the tempdb collation does turn out to be a problem, I would experiment changing the view's SELECT statement to COLLATE the data columns and then you would not need to worry about specifing COLLATE in every query where the view is consumed.
2
ste5anSenior DeveloperCommented:
For checking the collations:

EXECUTE sys.sp_MSforeachdb 'SELECT ''?'', CAST(DATABASEPROPERTYEX(''?'',''collation'') AS VARCHAR(255));';

Open in new window

and

EXECUTE sp_msforeachdb '
	SELECT	''?'' ,
			OBJECT_NAME(C.object_id) ,
			C.name ,
			C.collation_name
	FROM	?.sys.columns C;
';

Open in new window

1
Mark WillsTopic AdvisorCommented:
I do like Nakul's observation about tempdb, and using collate.

But would use an accent insensitive collation - a binary preferably,
try COLLATE SQL_Latin1_General_CP850_Bin
and that date needs to be '20180103'

the other possibility is non-printable characters

would be worthwhile checking the two columns dbo.MICU_Times_Analysis.Link and dbo.Sorian_Confirmed.Link

might want to limit the selections, but could be revealing to see the link in pairs...

select Link, 'A' as src, len(rtrim(ltrim(link))) as leng, patindex('%[^'+char(32)+'-'+char(126)+']%',link collate SQL_Latin1_General_CP850_Bin) as chr
from dbo.MICU_Times_Analysis
union all 
select Link, 'B' as src, len(rtrim(ltrim(link))) as leng, patindex('%[^'+char(32)+'-'+char(126)+']%',link collate SQL_Latin1_General_CP850_Bin) as chr
from dbo.Sorian_Confirmed
order by 1,2

Open in new window

2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Out of curiosity - what was it? Was it the collation of the tempdb or was it an unprintable character?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
T-SQL

From novice to tech pro — start learning today.