August 8, 2006 at 8:18 pm
hi everyone,
I'm new to this forum, i'm also new to SQL. i have found many thread in this forum regarding the problem i mention here.
but i'm still very blur with the explanation. i don't know how to start to import the VFP database into MS sql.
can anyone give me some step by step guide to import the data?
and what is DTS ? is it a script.... i'm noob, not really understand all this at all ...
the VFP database i got currently consist a lot of files. some with the extension .dbf and some dun have extension. what's the different with these files. ..
and most of the table in the VFP database consist of datetime format.
will it create any problem when import data ?
August 11, 2006 at 8:00 am
This was removed by the editor as SPAM
August 11, 2006 at 11:49 am
Hi,
I import some foxpro data daily but it is from foxpro 2.6 so maybe some issues I have may not exist any longer so you will need to verify against visual foxpro.
One thing that I found was that when you import a blank date into SQLServer, SQLServer interprets it as the date 12/30/1899. This may not be a problem if Visual Fox supports NULLS....
Dates tend to be the worst part of this. You will need to verify the date coming from the foxpro file will fit into the date range for valid SQLServer datetimes or smalldatetimes if that's what you are using.
From Books OnLine:
"Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.
The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute."
I connected to the foxpro tables via an ODBC data source.
One gotcha that caused me several days of sadness is that sometimes, the .dbf file remained open while the DTS package was still executing. At the end of my dts package, I delete the .dbf file I just loaded and copy a blank 'template' file in it's place. The process that pushes me data just appends to the blank file. From my world, I can't delete/pack so this was my best solution.
Oh yea, the gotcha.
Assuming you have a DTS connection to the foxpro file and a connection to the SQLServer destination, make sure you:
right click on the transformation
click Workflow Properties
click the options tab
check the box that says "Close connection on Completion"
Hope some of this helps,
Teague
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply