SQL Server 2005
(1)
Informix
(1)
WinSql
(1)
Database
(1)
ODBC
(1)
UCS
(1)
DSN
(1)

Connection problem to Informix from SQL Server 2005 with Unicode data

Asked By denis.carabadja
08-Dec-07 05:08 AM
I have Informix 9.4TC6 and SQL Server 2005.
DB_LOCALE in Informix DB is en_US.utf8 (en_US.57372).

First, I created DSN to Informix. Then tried to get data with WinSql
tool and it works fine.
But when I created linked server in SQL Server 2005 I have problems
with languages other than English.
I get a string like:
=E8 '=E5=B7 =E5=8C-=E5|=E5=B7=A5=E6=A5=E6 =AA=E51/4 =E41/4=9A=E7=A43/4

It seems to me that SQL Server splits 2-byte characters in 2 1-byte
characters.
If in options of linked server we set Collation Name to exact
language
we want --> then string data will be correct (of course only of
language that we choose).

Do you have any suggestions?????????

P.S. I don't want to use SSIS (DTS) for extraction from Informix DB

I found a solution.

Asked By denis.carabadja
08-Dec-07 05:11 AM
I found a solution.

SQL Server uses UCS-2 encoding, but Informix Database Server - UTF-8.
And I tried different ODBC and OLEDB drivers and they didn't
converted
from one encoding to another.
But, I am lucky :), I found a solution. I am using CLR possibilities
of SQL2005 and created a function that converts data from one
encoding
to another (http://msdn2.microsoft.com/en-us/library/ms160893.aspx).
Post Question To EggHeadCafe