March 8, 2004 at 9:32 am
Can somebody please tell me what is the most efficent way of transferring Foxpro DBF/CDXs into a SQL Server 2000 Database?
March 8, 2004 at 10:09 am
DTS will move the dbfs, select DTS, use a DBF data source and transfer the files. For the CDX, you will need to manually create the index.
I thought Visual Fox has a Upsize ot SQL wizard?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 8, 2004 at 10:17 am
Thanks for that. We, unfortunatley, are still using, Foxpro 2.6 as our back end. We will be upgraded to Visual Foxpro 8 shortly I've been told, although I've heard that before!
March 8, 2004 at 12:30 pm
DTS is, as Steve mentioned, the fastest way.
I believe also that the OLE DB driver for VFP will handle 2.6 tables without a problem, and may prove to actually work a bit better if any of the fields in the Fox table need to be re-mapped to different SQL datatypes. The biggest issue is empty date fields.
March 9, 2004 at 2:07 am
I work with FoxPro 2.6 Files and SQL Server 2000 - I use the Import Data Wizard (which uses DTS) and select dBase III as the file format. I have had few issues with this, except when dealing with a memo field. Then things get a little more tricky.
March 9, 2004 at 6:18 am
Flextech is correct. In my experience, date fields were the biggest problem. We had a FoxPro 2.6a application until 2001 (when it was converted to SQL Server and Powerbuilder).
The two issues I ran into using the DTS wizard were:
1. The wizard selected the smalldatetime data type for the SQL Server table, but that resulted in overflow errors. I think the empty dates also caused the overflow error. Changing 'smalldatetime' to 'datetime' overcame that issue.
2. Empty dates were converted to 12/30/1899 in SQL Server, requiring extra processing after importing. Since this was no big deal for us, we just ran a query after importing:
update <table> set <date column> = null where <date_column> <= '1/1/1900'
March 9, 2004 at 8:07 am
You can also use the DTS Multiphase Pump or a simple lookup script against the offending date column, all within the Data Transfer task.
March 10, 2004 at 7:13 am
I ended up having to first move the data into a table with all the date columns typed as Char. Then, you can check the dates to make sure they will fit into the datetime/smalldatetime ranges.
E.g. if the date in the foxpro table is 1215-01-01, SQLServer won't like it.
Teague
March 10, 2004 at 8:23 am
Teague brings up a good point. FoPro 2.6 dates range from 1/1/100 to 12/31/9999, which far exceeds the range of valid dates for SQL Server.
April 13, 2004 at 5:34 pm
I have been trying to import VISUAL FOXPRO 7.0 files into a SQL SERVER 2000 database. I am using the VFP OLE DB driver but everytime I get to the screen to select the source and the destination ..it doesn't show any source or destinition. Is there a better way to import ? Is there a step by step instruction I can look at or a different driver to use ? I'm in need of major help here... THanks everyone ...
Jim
April 20, 2004 at 9:21 am
I'm constantly transferring VFP 7 data to SQL 2000. The best driver to use is VFP OLE DB coming with VFP7. The easiest way to be sure you have the correct driver is to install VFP 7, or 8 on the machine. Although MS stated in an article that this is the ”only way to get the driver”, this is not absolutely correct, but it is simplest…
Install VFP, make DTS packet to import data (I usually use DBC radio button, but you could use the free structure as well), test the connection. In the transformation window make your mapping for the date fields to datetime to prevent errors with empty ones. Also, verify the “Not Null” in VFP to be correctly mapped in SQL with “not null”. Save the packet and run it. If you have any other errors, I’ll be happy to look at them.
MJ
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply