Use parameters in SQL Server referencing MySQL datasource-CodePudding

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?

CodePudding user response：

is the string concatenation operator in Microsoft's flavor of the SQL language, but this is not standard and it's not supported by MySQL.

Use CONCAT() instead in MySQL.

CodePudding user response：

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

CodePudding user response：

LIKE comparisons are affected by collation.

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

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