DTS to convert FoxPro DBF with memo fields

I need to convert a FoxPro DBF with memo fields to a SQL 7.0 table with DTS. I tried to do this, but I couldn't:
1. With SQL 7.0 (without SP1) it creates the table structure but it doesn't copy any records, giving the following message: "Invalid character value for cast specification".
2. Then I installed SQL 7.0 SP1 and it creates the table structure but it doesn't copy any records and it hangs.

(If the table has not memo fields it works without problem)

Is it possible to do this with DTS?
Is any other way or workarround?


Who is Participating?
madhuBConnect With a Mentor Commented:
In fact I had faced this problem when importing foxpro tables which has memo fields into sql server. The solution we found is

Open your foxpro application
/* Transfer the fields including memo to text file using LIST ..TO command */

use dbf
set memo to 10000
list field1,field2,... TO <\path\*.txt>

Finally use the text file as source in DTS to import it to sql server.
I had done this way, it works fine.
Brendt HessSenior DBACommented:
When it creates the table, what are the settings for the memo field in the SQL Server DB.  Are nulls allowed?  What data type (Text, ntext, varchar, nvarchar, ??) is the field?
When I was looking into this I found that I had to write my own App which extracted the data from FoxPro to text files and then BCP'd the data in.

I know now that DTS bulk copy is quicker than BCP so a better option might be to replace the BCP part with a DTS bulk copy package.

The other thing to note is that the SQL Server OLEDB Provider with MDAC 2.1.0 had big problems with memo fields. Also note that if you are using Foxpro 2.6 for Dos, in MDAC 2.1.1 onwards you have to use the Visual Foxpro ODBC driver.

Do you know VB?

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

analiafriAuthor Commented:
To bhess1:
When it creates the table, the settings for the memo field in the SQL Server DB are Text, nulls not allowed.

To SimonSabin:
I'm using the Visual FoxPro ODBC Driver.
I know VB. In fact I'm callin de DTS Package from a VB6 program.
Where can I see if I have MDAC 2.1.0 or MDAC 2.1.1?
A DTS bulk copy can do the conversion with FoxPro memo fields?  

If you go to


It is a utility that will find which version of the MDAC you have installed.

I know a DTS bulk copy can do the conversion, btu my understanding was this was not wokring for you.
Brendt HessSenior DBACommented:
If your source DB has any empty memo fields, modify the table to allow nulls and try again.  I've had a similar problem, and that fixed it.
analiafriAuthor Commented:
to bhess1:
I have had modified the table to allow nulls and that didn't fixed it.
But now I'm not sure if the problem is the memo fields because I tried to import a dbf that have not memo fields and it happend the same (creates the structure but dont insert any records.)
Perhaps I have problems with de foxpro driver. Do you know if I can update this driver, and how?

Have you found out what version MDAC you are running. Quick way what version of the Visual Foxpro ODBC driver are you using (look drivers tab of OBDC in controlpanel)
Brendt HessSenior DBACommented:
Okay, one thing to try is to place an error log in your DTS package.

In the Enterprise Manager, go into design on the package, select the transformation, and open it's Properties.

On the Advanced tab, enter an Exception File name (where data that has problems will be copied).

Save the package, then run it.

Open the exception file (probably in WordPad or some other text editor) and examine the data that caused problems.  Try to manually enter the data into the target DB.  If any field gives you a problem, that the likely culprit.
What format does LIST TO export in, i.e what delimiter is used.
I presume set memo to 10000 restricts the memo fields to 10000.

The first may cause a problem if the delimiter is in the memo field and the second may truncate your data.
analiafriAuthor Commented:
Even I think simonsabin is true about his last comment, the solution proposed by madHub was enought to arrive to a solution of my problem. Thanks.
The "Invalid character value for cast specification" message is usually associated with smalldatetime fields.  For some reason, the 'mm/dd/yy' format produces spurious results when translated to 'yyyy-mm-dd' format of SQL Server. Use the ttoc/dtoc(<date field1>) function(s) for all smalldatetime fields.  Also, bring the datetimes into varchar fields, or run an ActiveX Transformation on the date brought over to ensure that the date range falls in the proper SQL Server datatime/smalldatetime range.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.