Use parameters in SQL Server referencing MySQL datasource

Question

I have to run a query where some data comes from a SQL Server Database and some from a MySQL database. I managed to achieve it, but the problem comes when using a parameter with the MySQL database.

Here the faulty query :

select sp.SLC_ID, a.ASS_NAM, p.PCE_NAM, p.PCE_DES, pr.PRF_NAM,
(
    SELECT top 1 t2.CleTri
    FROM (
        SELECT CAST(t1.CLETRI as varchar) CleTri, CAST(t1.LIBELLE as varchar) Libelle
        FROM OPENQUERY("PLMStock",'SELECT CLETRI, LIBELLE FROM produits') t1 
    ) t2
    WHERE t2.Libelle LIKE pr.PRF_NAM + '%'
) as CLETRI
from SELECT_PART sp
left join PART p on p.PCE_ID = sp.PCE_ID
left join ASSEMBLY a on a.ASS_ID = sp.ASS_ID
left join PROFILE pr on pr.PRF_ID = p.PRF_ID
where sp.SLC_ID = 2930 and p.PRD_ID is null

When running this query, I got that error:

Msg 468, Level 16, State 9, Line 9 Cannot resolve the collation conflict between "French_CI_AS" and "Latin1_General_CI_AS" in like operation.

But when I hardcode the like parameter it works fine.

Can someone help me with it please?

Павел Сивоплясов · Accepted Answer · 2021-11-08 19:54:22Z

2

LIKE comparisons are affected by collation.

https://learn.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-ver15

WHERE t2.Libelle COLLATE database_default LIKE pr.PRF_NAM + '%'

answered Nov 8, 2021 at 19:54

Павел Сивоплясов

1485 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stu · Accepted Answer · 2021-11-08 19:53:20Z

0

The error indicates differing collations between the two columns, you need to convert one or the other to match.

Does the following work for you?

WHERE t2.Libelle LIKE pr.PRF_NAM + '%' COLLATE French_CI_AS

answered Nov 8, 2021 at 19:53

Stu

32.7k6 gold badges17 silver badges34 bronze badges

1 Comment

Charlieface Over a year ago

Probably best to collate the OPENQUERY rather than the local column, might help for sargeability

Collectives™ on Stack Overflow

Use parameters in SQL Server referencing MySQL datasource

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related