0

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'
XING
  • 9,608
  • 4
  • 22
  • 38
alonk
  • 101

1 Answers1

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

Community
  • 1
  • 1
XING
  • 9,608
  • 4
  • 22
  • 38