We have a DB2 database, from which we copy the data to a SQL Server 2000 database using a DTS package. The problem is with the Date Fields.Whenever we try to automatically copy them over, we get an error saying that there was an arithmatic overflow. We tried different stuff and decided to just create the table with the datafields changed to nvarchar. Anyone has any Ideas about how we can resolve this issue, or point me in a direction where I can find some information.

Thanking you in anticipation for your favor.
-----


Distributed computing ->Causality-> EventDriven ->We have the last laugh.!!

__________________
----

Distributed computing ->Causality-> EventDriven ->We have the last laugh.!!

What does the data look like in the DB2 database. Obviously, it is not compatable with the SQL Server DateTime datatype.

Jim

The table structure looks like this..
Column Name Type Length Nulls
-------------- -------------- -------
UPDT_USER_ID CHARACTER 8 N
UPDT_TIMESTAMP TIMESTAMP 10 N
ACTVY_STTUS CHARACTER 1 N
ID CHARACTER 9 N
FUND_ACCT CHARACTER 7 N
GIFT_DATEDB DATE 4 N
GIFT_AMT DECIMAL 9 N
.
.
.

Data looks like this...
__________________________________________________ __________
UPDT_USER_ID| UPDT_TIMESTAMP | ACTVY_STTUS| ID |
__________________________________________________ __________
cindyk | 1/2/2004 7:37:21 AM| I | 000632910|
__________________________________________________ __________
FUND_ACCT | GIFT_DATEDB | GIFT_AMT |
__________________________________________________ __________
B17513 | 1/2/2004 | 1007.5 |


----

Distributed computing ->Causality-> EventDriven ->We have the last laugh.!!

What is the exact error. Aritmatic overflow ususally refers to a numeric column. Can you also post the structure of the table you are triying to import to?

This is the structure of the table the data is going into
[GIFT_ACTVY] (
[UPDT_USER_ID] char (8) NOT NULL,
[UPDT_TIMESTAMP] datetime NOT NULL,
[ACTVY_STTUS] char (1) NOT NULL,
[ID] char (9) NOT NULL,
[FUND_ACCT] char (7) NOT NULL,
[GIFT_DATEDB] smalldatetime NOT NULL,
[GIFT_AMT] numeric (11,2) NOT NULL,
[INCM_TYPE] char (1) NOT NULL,
....)
The error says:"Number of failing rows exceeds the maximum specified. DateTime Field overflow."

This error occurs when I use ODBC Source for transformation. If i use OLEDB drivers, it says "Arithmatic overflow converting DataTime field"
I used individual Datetime string field transformations for the fields with DataTime. And rest of the columns were transformed using CopyColumn.

Thank you! Hope this gives you a little more information to help me.

----

Distributed computing ->Causality-> EventDriven ->We have the last laugh.!!

I am not sure what you mean by you used DateTime string field trnasformations. Try using copy column. Also check the date data. Could some dates be bad and outside the scope of a smalldatetime as opposed to a datetime?

First of all "Thank you! benson for patiently replying to my posts" :) I appreicate it.

I tried CopyColumn transformation as well. It gives me the same error. Most of the dates we have are with in the last 30yrs. So, I dont think they are out of the scope of either datetime and smalldatetime. The problem I guess is that DB2 and SQL Server2000 datatypes do not match. But, since we all know that. I thought there was some way of getting the date information with having to convet it into character strings.



----

Distributed computing ->Causality-> EventDriven ->We have the last laugh.!!

Wish I could be of more help.
The data you showed, looks compatiable with Sql Server. But I guess if you are getting errors, you will have to convert to a VarChar first.

Hi, this was the error i keep getting.
Error Source: Microsoft OLE DB provider for SQL Server
Error Description : The number of failing rows exceeds the maximun specified.
Insert error, column 26('PLDG_BILLING_DATEDB', DBTYPE_DBTIMESTAMP), status 6: Data overflow
Invalid character value for cast specification.

Changed that field taking your advice. works like a charm. :) thank you! very much Mr.Benson.

----

Distributed computing ->Causality-> EventDriven ->We have the last laugh.!!

Glad I could help...
Jim