2
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?

HappyTown
  • 6,036
  • 8
  • 38
  • 51
preston
  • 145
  • 2
  • 12
  • which server is linked (another mssql, or oracle, db2, ...)? what are the column datatypes? https://social.msdn.microsoft.com/Forums/sqlserver/en-US/acb718fd-0943-4e1c-95b0-067361157821/how-to-use-openquery-for-utf8-character?forum=transactsql – Cee McSharpface Feb 15 '17 at 08:55
  • It is a MySql database, I think. The datatype is most likely varchar – preston Feb 15 '17 at 08:57
  • it may be necessary to configure the settings "collation compatible" and "collation name", or to cast the varchars to nvarchar, see MSDN article in prev. comment. what is happening with the characters? substituted by question marks, or omitted, or Å > A? – Cee McSharpface Feb 15 '17 at 08:57
  • Ø gets converted to "ø", for example. I tried to cast pe_name to nvarchar(1000) as per your link (thanks), but got an error. How do I mess with the settings you mention? – preston Feb 15 '17 at 09:05
  • What's the configuration for the linked server? What are you using as an adapter to MySQL? Part of the issue here is that MySQL uses UTF-8 whereas SQL Server's `nvarchar` is UTF-16. So, some translation work is necessary and ideally your linked server driver would be doing it for you. – mroach Feb 15 '17 at 09:29
  • I'm looking at the configuration right now in SSMS. Collation Name is not set, perhaps this is an option? Collation Compatible is False and Use Remote Collation is True. The Provider is Microsoft OLE Provider for ODBC Drivers – preston Feb 15 '17 at 09:36
  • Setting the Use Remote Collation to False made no difference. – preston Feb 15 '17 at 09:54

1 Answers1

2

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.

Community
  • 1
  • 1
Cee McSharpface
  • 8,493
  • 3
  • 36
  • 77
  • Sadly, I get "Query completed with errors" and no error description for ucs2, "Unknown character set" for utf16. Also: "Cannot get the column information from OLE DB provider "MSDASQL" for linked server "DATABASE_LINKED". – preston Feb 15 '17 at 15:25
  • and pe_name is absolutely spot on. – preston Feb 15 '17 at 16:15
  • if you have access to the mysql database, you could try to run the inner query (with the cast) directly on mysql. if that succeeds, make a view out of it and try to select from there. – Cee McSharpface Feb 15 '17 at 16:39
  • I found a link to a function that fixed the problem: http://stackoverflow.com/questions/28168055/convert-text-value-in-sql-server-from-utf8-to-iso-8859-1. So now the code looks like this: – preston Feb 15 '17 at 16:41
  • 1
    SELECT id , pe_department_id , DATABASE.dbo.UTF8_TO_NVARCHAR (cast(pe_name AS varchar(MAX))) , 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') – preston Feb 15 '17 at 16:42
  • You guided me in the right direction so please leave an answer so I can accept with a thank you :). – preston Feb 15 '17 at 16:44
  • I just edited the final version into the answer, may it help others in the future. – Cee McSharpface Feb 15 '17 at 16:52
  • Thank you dlatikay, I will make sure to to that from now on. – preston Feb 16 '17 at 13:05