ASCII characters above 127 not translating through linked server

Question

ASCII characters above 127 not translating through linked server

jcook119

Old Hand

Points: 379
More actions
February 8, 2005 at 2:23 pm

#163338

Good afternoon,
We have an Oracle database that holds a table we need to pull over into SQL Server 2000. The source table has a field that contains character strings which freqently include ASCII characters between 128-256. A developer has set up a linked server on the SQL 2000 server, using the "Microsoft OLE DB Driver for Oracle" driver. When I run a query in Query Analyzer to examine the Oracle field's data, any ASCII characters greater than 127 come across as the standard Windows "unprintable" character (empty box). By using the CHAR() function, I have verified that SQL Server is aware of the characters in this range, but the linked server query is not returning them. Is there a 7-bit ASCII limitation on the OLEDB driver?
Thanks,
James

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Dunno about any limitations, I don't think there are, this is just the ususal pain us extended ascii users can expect.

This is also the major reason I never do linked servers, it's too much hassle with the ANSI/OEM conversion stuff to be worth it imo. Instead, whenever I need something from remote, I bounce it on local disk first, then bcp into my staging area. That way I can use bcp with the proper settings accordingly. Of course you can still use a linked server, and just land the result as a file instead of directly into a table, but it sort of takes away the beauty of linked server...

Anyways, in my experience to get full control of ANSI/OEM conversion, you need some way to control that as close as your destination as possible. Linked servers doesn't grant that control, unfortunately. Or, perhaps they do, but you can't in any easy way verify from time to time what the chars relly looked like, since you won't have any history to go back to..

/Kenneth

cpack Ten Centuries Points: 1299 More actions · Answer 2

When I have linked to Oracle servers in the past I always used the OEM Oracle client driver rather than Microsoft's. I have never run across issues with ASCII translation, but I have not linked to Oracle extensively.

HTH

Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

ANSI/OEM conversions is a major issue for everyone dealing with extended ASCII, and is something you always need to lookout for.

Here's a few links more or less on the subject that may be useful

http://support.microsoft.com/kb/q193135/

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarsqlsg/html/msdn_interntl.asp

http://www.microsoft.com/globaldev/getWR/steps/wrg_codepage.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c07ppcsq.mspx

/Kenneth