Connection problem to Informix from SQL Server 2005 with Unicode data

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|å·¥æ¥æ ªå1/4 ä1/4šç=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

Re: Connection problem to Informix from SQL Server 2005 with Unicode data

(denis.carabadjac@gmail.com) writes:
> 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:
> è 'å· å?-å|å·¥æ¥æ ªå1/4 ä1/4?ç¤3/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?????????

This looks like a difficult case. SQL Server does not support UTF-8.
You can set a server option, "Collation name" as you found, but that
should be a collation that exists in SQL Server, but SQL Server does
not have any UTF-8 collations.

I have no experience with Informix, but it seems to me that either you
need to find a way to convert UTF-8 to UTF-16, or you will have to
go over the file system. The tool BCP as well as the command BULK INSERT
is able to handle files with UTF-8 data.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx