Importing FoxPro tables into SQL Server 2000 (DTS) 1

[Dabase]

Hi,

I am trying to import some foxpro tables into SQL Server 2000 using DTS. I am using a DSN (Microsoft Visual FoxPro Driver) free table directory to connect to the foxpro tables from SQL Server.

I am getting the following errors when copying the data from one of the foxpro tables:

Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error, column 93('userf20', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error, column 92('userf19', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error, column 91('userf18', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification
Invalid character value for cast specification
Invalid character value for cast specification

Any ideas what could be causing this?

Thanks
Dabase

 

[SqueakinSweep]

It could be that the some of date fields in your Fox Tables are empty.


Sweep
...if it works dont mess with it

 

[Dabase]

Hi Sweep,

Some records on the table do contain blank values on some of these fields, but I would still like to import the entire table.

Thanks
Dabase

 

[SqueakinSweep]

SQL will not allow Blank Dates...so you would be advised to update your fox tables with a null date of your choosing. Ive always used 01/01/1900. Once you've eliminated any empty date fields you should find the table imports without problems.



Sweep
...if it works dont mess with it

 

[fluteplr]

Also make sure you are importing into a datetime not a smalldatetime if there is any chance any of the dates in the VFP table fall outside the smalldatetime fields acceptable range.

 

[apcapc]

Can someone please advise what dates are supported by smalldatetime

Thanks

Andy

 

[rgbean]

From the SQL Server "Books Online":

smalldatetime

Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

You can get your own copy at

http://www.microsoft.com/downloads/...1-A420-445F-8A4B-BD77A7DA194B&displaylang=en.

Rick