Keep danish characters (Æ Ø Å) in Openquery to linked server

Question

select * from openquery(DATABASENAME_LINKED, 'select id, pe_department_id, pe_username, pe_name, pe_hours, pe_startpage, pe_admin from person')

Special characters are lost in this query. Is it possible to ensure a certain encoding in the code or somewhere else?

Answer 1

Repurposing the function UTF8_TO_NVARCHAR from this article, it will be possible to do the conversion on the fly:

SELECT 
    id,
    pe_department_id,
    dbo.UTF8_TO_NVARCHAR(cast(pe_name AS varchar(MAX))) pe_name,
    pe_hours,
    pe_startpage,
    pe_admin
FROM OPENQUERY(
    DATABASE_LINKED,
    'select
        id,
        pe_department_id,
        pe_name,
        pe_hours,
        pe_startpage,
        pe_admin 
    from person'
)

The more intuitive approach of casting the column with MySQL's CHARSET directive right inside the pass-through query failed with no useful error description, so we did not pursue this further.