Hi,

should we expect DTS transfers to generate corrupted data ?
If we have a clear, repeatable instance, then should we
expect a fix ? Or, should we rather consider it as an
inherent limitation of the BCP libraries, which attempt to
format entire blocks of data, and may go wrong if we supply
the incorrect datatypes.

Corruption has been discussed before, e.g. at:
http://www.dbforums.com/showthread.php?threadid=757658

Now, we came upon a case which is milder, but quite stable.
It shows on our production machine, but we could also
reproduce it in a test environment.

We have a datapump, i.e. Transfrom Data Task, which gets
records from Oracle via ODBC (not OleDb). One of the fields
is declared numeric(16,2) on the Oracle side. By mistake,
the corresponding field was declared decimal(18,0) in Sql
Server ( v 7.0 ). In real life, the field represents a
dollar amount, with cents.

When DTS transfers the data, it obviously loses the cents.
But funny things happen when the amount is negative. The
negative amount is still inserted. But all Sql Server
client tools (BCP, ISQL, ODBC, OleDB ) display the amount
as if being positive. However, internally it is still
recognised as negative:

select amount, account
from my_table
where amount < 0.0

displays positive amounts. But it only finds the few rows
for which the amount was negative in the first place in Oracle.

This is not a once-only occurance. The DTS package drops
and recreates the table every day, and the problem persists
for quite a while. In the test database, however, we
replaced the decimal(18,0) datatype with something more
appropriate (e.g. money ), and everything was fine.

Anyone heard about anything similar ?

Andrew