Error Exporting FoxPro 2.5 DOS table to SQL Server

Using the Import/Export Data Wizard, I'm trying to export a FoxPro 2.5 DOS (as dBase III) table of 15,000 records to SQL Server 2000. I keep getting this error message:
Insert Error, Column 32 ('PROG_START',DBTYPE_DBTIMESTAMP), Status 6: Data Overflow.
Invalid character value for cast specification.

I have SQL Server create the table each time I run the wizard. The new table allows NULLS in this column and I made sure to overwrite the empty date fields in the FoxPro table with blanks to make sure it would result in NULL. Originally SQL Server tried to put this as SMALL DATETIME, but when I got the message earlier, I changed it to DATETIME.

Any suggestions?hi

1) can you run a select in the fox tables, for example:
select min(col), max(col) from myTable

2) do you use any function to transform the fox date to sql date?

TIA
Abel.|||My first guess would be that you've got some data for 02/29/00, which Fox mis-interprets at 1900-02-29 (which it shouldn't accept, but it did).

If that isn't correct, the only thing I could offer would be that if you'll post the DBF (and any associated NDX, NTX, or CDX) files, I'd be willing to take a shot at converting them tonight.

-patP|||The problem is that it isn't always the same record that I'm getting the error. I'm getting this error with different tables I'm trying to convert and they all have numerous records with blank Date fields that are causing the problem.
Pat, thank you for your offer. I can try to come up with a sample data set to send to you or give you one of the smaller tables that give this error. Which would you prefer.

Error converting data type

Hello,
I got error when i trying to import data from foxpro table to sql:
SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft Visual
FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
[MY.DBF]')
Server: Msg 8114, Level 16, State 10, Line 1
Error converting data type DBTYPE_DBDATE to datetime.
how to fix? Thanks.I don't know Foxpro; is there some way you can convert the record_dt column
into, perhaps, a string in the OPENROWSET, then convert the column to a
DATETIME back on the SQL Server side? It's just a matter of incompatible
formats...
Something like:
SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft Visual
FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select
CONVERT_TO_STRING(record_dt) from
[MY.DBF]')
Replace CONVERT_TO_STRING with however you convert to a string in Foxpro.
By the way, the safest format to use for dates in SQL Server is the ISO
format: YYYYMMDD HH:MM:SS.MS
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"js" <js@.someone@.hotmail.com> wrote in message
news:O2WDB6L5FHA.3908@.tk2msftngp13.phx.gbl...
> Hello,
> I got error when i trying to import data from foxpro table to sql:
> SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft Visual
> FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
> [MY.DBF]')
> Server: Msg 8114, Level 16, State 10, Line 1
> Error converting data type DBTYPE_DBDATE to datetime.
> how to fix? Thanks.
>
>|||Apparently some of the records are not in datetime format -
try to add where isdate(trade_dt)= 1
SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft
Visual
FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
[MY.DBF]') where isdate(trade_dt)= 1
to see the offending records run the following :
SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft
Visual
FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
[MY.DBF]') where isdate(trade_dt)= 0|||Thanks for the help.
Got this error:
Could not execute query against OLE DB provider 'MSDASQL'.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandText::Execute returned
0x80040e37].
I found some values are '1899-12-30 00:00:00.000', how to fix this in a
query FoxPro can run?
<hussein.safa@.gmail.com> wrote in message
news:1131498021.441399.109230@.g44g2000cwa.googlegroups.com...
> Apparently some of the records are not in datetime format -
> try to add where isdate(trade_dt)= 1
> SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft
> Visual
> FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
> [MY.DBF]') where isdate(trade_dt)= 1
> to see the offending records run the following :
>
> SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft
> Visual
> FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
> [MY.DBF]') where isdate(trade_dt)= 0
>