I am using Microsoft SQL Server 2019 15.0.2104.1 to query an IBM Informix Dynamic Server Version 12.10.FC5XT database via ODBC/OPENQUERY.
I have an issue when any field contains special characters ODBC Interprets the character encoding incorrectly and fails to convert it to a valid character or character set and crashes/errors. The errors can be different, the latest one was failing to interpret '(•ˋ _ ˊ•)' as special characters and instead try to convert it to a datetime2 object
Other errors vary and they are sporadic.
My issue is I need to query this table constantly for a few things (a few times a minute) and the table contains a lot of fields which are free text editable and special characters are a constant issue (don't ask me why).
I attempted to rectify this a while ago and went down a rabbit hole of region encoding between ODBC and Informix and attempting to match them but had no luck.
Has anyone else encountered this, and if so, were you able to resolve it ?
This comment by @user2722968 fixed my problem for me, if anyone else is experiencing the same issue
Identifiers and such are not part of the database locale encoding, but of the odbc encoding itself. Find your odbc.ini (e.g. using odbcinst -j) and try adding UNICODE=UCS-2 under the (possibly newly created) [ODBC] section. That should convince the odbc driver that it itself needs wide-character encoding.