4

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

"linked server "PROD" reported an error. The provider did not give any information about the error...Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "PROD".

I can narrow the problem to one row and when I run this query for that row I get a different error:

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

Error:

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

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

And I can query the offending NAME column as a DUMP, like this:

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

Which returns:

Typ=1 Len=16: 77,73,88,84,69,67,79,32,68,69,32,84,73,68,65,193

then rebuild using SELECT CHAR(77) + CHAR(73) + ..., and I get "MIXTECO DE TIDAÁ". Bottom line, it seems, is that CHAR(193) in the Oracle data is causing my query to fail. But how to fix?

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

ORA-29275: partial multibyte character

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

Action: Ensure that the complete multibyte character is sent from the remote server and retry the operation. Or read the partial multibyte character as RAW.

However, I don't know how to "Ensure..." and I don't know how to "read... as RAW".

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

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

In SQL, sp_helpsort returns:

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

In Oracle, the NLS_CHARACTERSET is: AL32UTF8

Any help re: why this is not working or how to get this working? Let me know if need further info.

Improve this question

1 Answer 1

Reset to default
3

The 193 stored in the Oracle database is not a valid character in the UTF-8 character set. UTF-8 encodes the first 128 characters (0-127) using a single byte but anything beyond 7-bit ASCII requires two or more bytes of storage. Whatever application inserted this data appears to be doing so incorrectly, most likely because it is misconfigured to bypass the character set conversion that is supposed to happen when data is transferred between the client and the database.

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

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

4 Comments

OK, thanks. I did a little more research on character encoding based on your tip. I am not sure how the data got in there and my co-worker was not either. Probably it was old data imported somehow from somewhere. Also, the NLS_LANG is ‘AMERICAN_AMERICA.AL32UTF8’.
The query was failing on two tables. We changed one item of data in each table, and everything worked. It is interesting that old setup (SQL Server 2005, Oracle provider 10g) did not choke on the bad data, it just ignored it. In one case it substituted the replacement character (U+FFFD) and in the other case it just truncated at the offending character.
@JoeMcCarthy - If the client NLS_LANG character set matches the database server's character set, Oracle will bypass character set conversion when data moves between client and server. That means that the client application is completely responsible to actually send valid UTF-8 data to the database. If the client application sends data in some other encoding (Windows-1252, ISO-8859-15, etc.), you'll end up with invalid data in the database. It's pretty rare that a client NLS_LANG ought to specify an AL32UTF8 character set.
I'm sorry, I misread your question. I gave you NLS_LANG for the server. The only place I could find the NLS_LANG on the client was in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME 1\NLS_LANG. It is AMERICAN_AMERICA.WE8MSWIN1252. I guess that's the NLS_LANG that the Oracle provider is using as there are no Oracle instances running on the client machine. What's going on under the hood is still a little murky to me, but I'm glad our queries are working and I thank you again for your help.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.