Sql-server – Oracle to T-SQL OPENQUERY special character conversion issues

collationencodinglinked-serveroraclesql-server-2008

I'm struggling to figure out where the character encoding issue on my Linked server may be coming from here. The ZPDT_PAT_ALPHA column should have a degrees symbol at the end, as shown by the DUMP. 

SELECT *, CHAR(176)
FROM  OPENQUERY([R_Pronto], 'SELECT DUMP(ZPDT_PAT_ALPHA), ZPDT_PAT_ALPHA
            FROM LINKED.ZCUSPRODATTR0')

enter image description here

Here are the NLS parameters. NLS_LANG is set in the registry "AMERICAN_AMERICA.WE8MSWIN1252"

enter image description here

And the linked server settings using Microsoft OLE DB Provider for ODBC Drivers
enter image description here

I appreciate any pointers as to what I can adjust to make the special characters appear correctly. I unfortunately don't have access to the Oracle DB to make any changes.

The application that sits on top of this DB is able to show the character – I believe it interfaces directly with Oracle.

enter image description here

Thanks

Edit: Showing the TRANSLATE USING NCHAR_CS

Edit2:
enter image description here

Best Answer

The solution was to change the NLS_LANG value in the registry to "AMERICAN_AMERICA.US7ASCII" because of reasons described in these answers:

Now the characters are coming through correctly.

Thanks @wernfried-domscheit !

Related Question