DTS changes character > 128
-
For you DTS experts out there, please help!
On SQL 2000: I am copying data from an Oracle server (ERP server) to a SQL server (Reporting server) using DTS. Straight copy, no conversion. Yesterday I received a complaint that a person's surname was misspelled on the reports but it was correct on the ERP system. I found that the DTS copy is changing the character from decimal 214 to 86.
Does anybody know how to get around this?
5ilverFox
Consulting DBA / Developer
South Africa -
128 + 86 = 214 ... coincidence ??
What is the datatypes of the source and destination fields. What collation are you using in SQL Server?
--------------------
Colt 45 - the original point and click interface
-
Coincidence? I don't think so...
The datatypes are char (30) on both sides. My collation name on SQL is SQL_Latin1_General_CP1_CI_AS.
5ilverFox
Consulting DBA / Developer
South Africa -
As Phil pointed out, it looks like the character is being converted to 7-bit ASCII (the high order bit is set to zero). However, the character Char(214) is supported by the collation SQL_Latin1_General_CP1_CI_AS.
214 dec = 11010110
86 dec = 01010110
It must be something in the Oracle driver. As I don't have access to Oracle, I can't check. I'd look for a setting related to ASCII/ANSI, etc.
-
I have taken your advice and tried different drivers like: MS OLEDB provider for Oracle, Oracle provider for OLEDB, MS ODBC Driver for Oracle and the Oracle ODBC Driver, but everything came back with the same answer. I also tried different APPS like DTS on SQL2000, SQLPlus on Windows, Crystal Reports and VB6. I even tried the Crystal Reports direct connector for Oracle, They all returned the same character. If I use the same query in sqlplus directly on the Oracle server (UNIX) it returns the correct data. I think I will have to take this query to the Oracle people.
5ilverFox
Consulting DBA / Developer
South Africa
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply