Solved

DTS to convert FoxPro DBF with memo fields

Posted on 2000-03-20
12
1,883 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

831 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