Posted by Erland Sommarskog on 03/08/06 00:28

(kenneth.osenbroch@telenor.com) writes:
> I have a MSSQL Server communicating with an Oracle database through a
> MSSQL linked server using a MS ODBC connection.
>
> If I query the Oracle database through the Oracle ODBC 32Bit Test, the
> result is fine:
>
> select addrsurname from address where addrnr = 6666;
> HÅKANSSON
>
> If I do the same query within the SQL Query Analyzer (using the linked
> server), I get:
>
> select * from openquery(TESTSW, 'select addrsurname from address where
> addrnr = 6666');
> H?KANSSON
>
> I have tried to both check and uncheck the Automatic ANSI to OEM
> conversion, but the result remains the same.
>
> Does anyone know what to do to make the result display the special
> characters in SQL Query Analyzer?

Obviously there is a collation clash of some sort between Oracle and
SQL Server.

I don't know Oracle, but what data type is addrsurname? Is it varchar
or nvarchar (or whatever they may be called on the Oracle side)? If
it is some 8-bit data type, what is the code page for that column?

If I am to guess, I hold the ODBC driver as the prime suspect. Define
the linked server with the MSDAORA provider instead, or even better
Oracle's own OLE DB provider. (MSDAORA does not support Oracle 9 and
10, I believe.)

There are a few options to set for linked servers in SQL Server, but
I don't really think thees are the knobs to pull here.


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

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

[Back to original message]