Special characters in MSSQL.

[kenneth.osenbroch]

Hi,

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?
Thanks,
Kenneth

[m.bohse]

Kenneth,

obviously your SQL server and the Oracle DB use a different collation.
In your linked server properties on the tab server_options you can
change the option "use remote collation" from True to False. That
should solve your problem.

Another option would be to convert the character values to
your(preferred) collation like this:
SELECT CAST(addrsurname AS varchar(25)) COLLATE French_CS_AS -- Fill in
your collation here
Good luck
Markus

[kenneth.osenbroch]

Hi Markus, and thanks for your response.

I tried both your suggestions without any luck. Do I need to restart
the MSSQL Server service or something?

Thanks,
Kenneth

[Erland Sommarskog]

(ke***************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[kenneth.osenbroch]

Hi,

Creating the linked server with Microsoft OLE DB Provider for ODBC
Drivers solved the problem. Thanks for your help.

Cheers,
Kenneth