Workaround 1 - use native Unicode column

When the TO_NCHAR capability is disabled, the string comparison for Unicode String columns is not delegated to the remote query and the comparison is done in post-processing.

SELECT e."Name" Element, ea."Name" Attribute,
ea."Value"
FROM Element.Element@pi e
INNER JOIN Element.Attribute@pi ea ON ea."ElementID" = e.ID
WHERE ea."Name" = 'Plant'
AND ea."Value_String" Like N'H%'
ORDER BY e."Name", ea."Name";