1

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

3
  • 1
    I doubt you can accurately register a character that requires 8-bits (char(178)) using a character set that acknowledges only 7. (US7ASCII). Dec 24, 2019 at 1:35
  • Thanks @MichaelKutz, I've added to the post showing the application which is able to show the degrees symbol, which is what makes me believe the issue is in the server linking.
    – blakel
    Dec 24, 2019 at 2:26
  • 1
    When your database character set is US7ASCII then it is not possible to have a degrees symbol ° at the end. Probably you will ask: "Why do I see then ° in the application?" - See answer: stackoverflow.com/questions/47039666/… and/or stackoverflow.com/questions/36710360/… Dec 31, 2019 at 15:28

2 Answers 2

2

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 !

1
  • 1
    Interesting that in Oracle, the US7ASCII encoding completely ignores characters that use the 8th bit (i.e. values 128- 255), and stores/retrieves them as is. That explains how you are able to see the degree symbol. And, that also implies that the conversion problem occurred in Oracle (not in the driver or in SQL Server) since code page 1252 does have that symbol for that same value, so it must have converted that byte to ¿ going into code page 1252 from 7-bit ASCII, most likely as a result of the conversion process assuming that 7-bit ASCII couldn't have such a value in the first place. Jan 2, 2020 at 17:10
0

The NLS parameters you posted are for Oracle client connections. Since you are not actually using an Oracle client, I doubt they will be relevant. (They look like default values.) To confirm the database character set, run:

select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET'

If it is a recent version of Oracle, I'd expect it expect it to say 'AL32UTF8' i.e. UTF-8.

If so, your issue might be trying to convert the UTF-8? nvarchar is stored as UCS-2. I can't write the code because I don't have a suitable test environment, but maybe something like:

declare @result nvarchar(255);
set @result = (select... )

Assuming it is a singleton select. Maybe try an explicit cast as nvarchar? SQL Server 2019 has some UTF-8 support, I haven't played with it though.

HTH

1
  • Thanks for your response. The NLS_CHARACTERSET is set to US7ASCII. Sadly even with the explicit cast I still get the result of '24-29 @ 20¿'
    – blakel
    Dec 31, 2019 at 0:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.