analiafri
asked on
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?
Thanks.
Analía
analiafri@grupoa.com.ar
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
Brendt Hess
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?
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?
ASKER
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.
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.
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.
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.
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.
ASKER
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.
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.
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)
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.