Solved

DTS to convert FoxPro DBF with memo fields

Posted on 2000-03-20
12
1,830 Views
Last Modified: 2013-11-30
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?

Thanks.

Analía
analiafri@grupoa.com.ar
0
Comment
Question by:analiafri
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 2637522
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?
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 2638002
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?

0
 

Author Comment

by:analiafri
ID: 2639826
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?  

Thanks.
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 2640227
If you go to

http://download.microsoft.com/download/dasdk/install/1.0/WIN98/EN-US/cc.exe

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.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2640895
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.
0
 

Author Comment

by:analiafri
ID: 2641102
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?

thanks.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 7

Expert Comment

by:simonsabin
ID: 2641117
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)
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2641184
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.
0
 

Accepted Solution

by:
madhuB earned 200 total points
ID: 2644064
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.
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 2644109
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.
0
 

Author Comment

by:analiafri
ID: 2666820
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.
0
 
LVL 1

Expert Comment

by:jg4smile
ID: 3689915
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.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now