Oracle Database Discussions

Hi,

We have an Oracle 11g (11.1.0.7.0) database running on a Windows 2003 SP1 server. Heterogeneous connectivity has been implemented to an MS SQL Server (2002) database. Whilst the link itself is working correctly, with queries returning expected results, we are however experiencing a problem with character set conversion. For example the French character é (e-acute) is not being translated correctly and results in an "ORA-29275: partial multibyte character", when queries are made against the table row containing this character.

The SQL Server database uses the ISO8859-1 character set and our database is using AL32UTF8.

In the initSID.ora configuration file for the heterogeneous link, we have defined the character set (used by SQL Server) as follows:

HS_LANGUAGE=american_america.we8iso8859p1

However this has not resulted in character set translation taking place.

We have also tried adjusting the "Perform translation for character data" option in the MS ODBC driver. This has not yielded the desired result either.

What I would like to know is:

(1) How to correctly configure a heterogeneous link to perform character set translation?

(2) If (1) above is not possible, then what alternative options are there, to achieve this?

Thanks,
William Walker