DTS Import of Foxpro DB

[CliveW]

Hi,
I'm trying to import a Foxpro database into SQL2K, I have come across a problem trying to import 1 table. It has 15 fields and fails to import anything more than 790 rows at a time. The error message is dbdatetime overflow error. I have 325000 rows to import and don't want to do them in small chunks. If I omit the DOB(smalldate) column out of the import then all the rows will be imported without a problem. Can anyone help with the conversion from foxpro date format field to a sqlserver 2000 smalldate format.

Thx Clive

 

[redlam]

Hi Clive -
I believe the problem here is that ODBC is interpreting the foxpro dates as smalldatetime - at least this is the case for me here. You'll need to change the datatype of the destination table on your SQL server to datetime.
Hope that helps.

 

[CliveW]

thx have just tried that and still errors

"The number of failing rows exceeds the maximum specified insert error, column 12('dob', DBTYPE_DBTIMESTAMP), status 6: data overflow. Invalid character value for cast specification."

 

[redlam]

Hmmm... not sure what else could be the problem. My experience with ODBC and foxpro tables has been this:
Any foxpro date field that does not allow nulls but is empty will be interpreted by ODBC as a default date of 12/30/1899 (this is by design - go figure). When you try to import this date into SQL, it is interpreted as a smalldatetime data type but it will fail because smalldatetime types will only accept values between 01/01/1900 through 06/06/2079. Everytime I have experienced the above error, this has been the case. I suppose its possible that you have another issue that is causing the exception. Usually SQL will return the row number that it failed on - see if you can review the data in that row to determine the exact problem. Sorry I couldn't be of more help.
Good luck!!