Sql server linked server to Oracle return ??? instead of chars

Question

I am using Oracle 11g and Sql Server 2008 R2 In the sql Server i have a linked server connected to the Oracle machine. In the Sql Server I am using openquery to select from a table in the Oracle. Whenever there are char columns in the Oracle i get all data like ??????? I try to modify the collation in the Linked Server properties windows - still get the same ??????

How can i resolve this issue?

here some more details about the configuration:

Details from Oracle nls parameters :

NLS_LANGUAGE    AMERICAN

NLS_TERRITORY   AMERICA

NLS_ISO_CURRENCY    AMERICA

NLS_NUMERIC_CHARACTERS  .,

NLS_CHARACTERSET    IW8ISO8859P8

NLS_SORT    BINARY

NLS_COMP    BINARY

NLS_LENGTH_SEMANTICS    BYTE

NLS_NCHAR_CHARACTERSET  AL16UTF16

the linked server has this options :

'collation compatible', 'false'

'data access','true'

'dist', 'false'

'pub', 'false'

'rpc', 'false'

'rpc out', 'false'

'sub', 'false'

'connect timeout', '0'

'collation name', 'Hebrew_100_CI_AS'

'lazy schema validation', 'false'

'query timeout', '0'

'use remote collation', 'true'

'remote proc transaction promotion', 'true'

Community · Accepted Answer · 2017-05-23 12:08:31Z

0

The issue seems to be due to mismatch between NLS parameters . Your Oracle NLS_LANG = 'AMERICAN' where as in sql server it shows something like collation name = 'Hebrew_100_CI_AS'. Try setting both same.

ref: PL/SQL Developer displays does not display Hebrew characters properly

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Aug 31, 2016 at 12:47

XING

9,9264 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sql server linked server to Oracle return ??? instead of chars

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related