All geek questions in one place

Character encoding for SQL Server for Oracle linked server

This linked server worked fine before we upgraded it from SQL Server 2005 to 2008R2, but now it throws this error when querying from certain tables (it still works for other tables):

The "linked server" PROD "reported an error. The provider did not provide any error information ... Failed to get the row from the OLE DB provider" OraOLEDB.Oracle "for the linked server" PROD ".

I can narrow the problem down to one line, and when I run this query for this line, I get another error:

select * from openquery( PROD, 'SELECT ID, NAME FROM ITEMS WHERE ID = 5437')

Error:

The OLE DB provider "OraOLEDB.Oracle" for the linked server "PROD" returned the message "01".

The OLE DB provider "OraOLEDB.Oracle" for the linked server "PROD" returned the message "ORA-29275: partial multibyte character".

And I can query the offending NAME column as DUMP, for example:

 select * from openquery( PROD, 'SELECT DUMP(NAME) FROM ITEMS WHERE ID = 5437')

What returns:

Type = 1 Linen = 16: 77.73.88.84.69.67.79.32.68.69.32.84.73.03.68.65.193

then rebuild with SELECT CHAR (77) + CHAR (73) + ... and I will get "MIXTECO DE TIDAÁ". It appears that the output of CHAR (193) in Oracle data causes my query to fail. But how to fix it?

Oracle ( https://forums.oracle.com/forums/thread.jspa?threadID=551784 ) provides this cryptic tip:

ORA-29275: partial multibyte character

Cause. The requested read operation could not be completed because a partial multibyte character was found at the end of the input.

Action: Make sure the full multibyte character is sent from the remote server and retry the operation. Or read a partial multibyte character as RAW.

However, I do not know how to "Provide ...", and I do not know how to "Read ... like RAW".

SQL Server is a 64-bit version running on a 64-bit Windows Server 2008R2 system and the Oracle 11gR2 64-bit client is installed.

in SQL: NAME nvarchar (60) NULL column in Oracle: NAME varchar2 (60)

In SQL returns sp_helpsort:

Latin1 - General, case-insensitive, accent-sensitive, rope-insensitive, width-insensitive for Unicode data, SQL Server 52 sort order on code page 1252 for non-Unicode data

In Oracle, NLS_CHARACTERSET: AL32UTF8

Any help re: why doesn't it work or how does it work? Let me know if you need more information.

+4
source share
1 answer

193 stored in an Oracle database is not a valid character in the UTF-8 character set. UTF-8 encodes the first 128 characters (0-127) using one byte, but for anything other than 7-bit ASCII, two or more bytes of storage are required. Regardless of which application was installed, this data is not correctly received, most likely because it is not configured correctly to bypass the character set conversion that must occur when data is transferred between the client and the database.

What language / framework / API is an application that inserted data into an Oracle database using? What is the NLS_LANG client NLS_LANG ?

+3
source

Source: https://habr.com/ru/post/1399055/

More articles:


All Articles