Link to home
Start Free TrialLog in
Avatar of Dawn Cassara
Dawn Cassara

asked on

Nightly transfer of Visual FoxPro 9 database (Windows 8) to MS SQL 2016 (Windows 2012 R2).

I've tried everything I can find on the internet on how to get data from VFXP 9 on a Windows 8 machine to MS SQL Express 2016 on a Windows 2012 R2 server.  Two weeks I've been trying.
I've tried setting up a linked server, but the instructions on that assumes more knowledge than I have - I can't get it working and that may be because they were written for different machines than I'm using.
I need some help on database drivers and ODBC set up - I think I may have that, but not sure since I can't get linked servers working.
If I understand the concept of linked servers correctly, I could then write a PHP program to move the data from one to the other and set up a nightly run on the Task Scheduler.
I'd need a connection string to a DBF database container or the individual tables on a different machine to do that.
Worse case, I get the VFXP data into a CSV file and write a program to import that, but if linked server will work, that would be much better.
Anyone knowledgeable on this?
Avatar of JesterToo
JesterToo
Flag of United States of America image

Here is a link to a similar question that was asked and answered about 3.5 years ago.  It may contain enough information to get you going.

Not sure how difficult getting the right versions of drivers for your SS2016 db might be however.  Biggest issues may be finding the right OLEDB
driver file... gotta watch out for 32/64 bit mismatch.

Hope you find this useful.

https://www.experts-exchange.com/questions/26104121/Trying-to-link-FoxPro-files-in-MS-SQL-Server-2008-via-Linked-Server.html
Avatar of Pavel Celba
Forget about linked server. Yes, it is possible if you would have 32 bit installation of the SQL Server but no such edition exists for MS SQL 2016...

Are you able to connect to the SQL Server 2016 from your W8 machine via SSMS? If yes then it is also very easy to connect SQL Server from Visual FoxPro and export the DBF data to SQL Server tables. You will need just a few FoxPro commands to do it. Visual FoxPro also offers Upsizing Wizard but it has some drawbacks...

If you are not able to connect even via SSMS then you must solve this as the first step. Then let us know and we'll tell what to do next.
Avatar of Dawn Cassara
Dawn Cassara

ASKER

I'm not sure how to establish a connection with SSMS - it seems like I'd have to install MS SQL Express 2016 on that machine as well, which is not problem.  Would I also have to install an ODBC drive or would installing MS SQL create it?

Would I even need the same version of MS SQL - if a 32 bit one would solve any problems and would work, I could install that instead.

How would i connect via SSMS?  Is that just using a connection statement?  I have no idea how to form one in Foxpro script, but this sounds like a really good solution overall - and doable with a little help.
Are you for hire?
If you install the SQL Express on the same machine where you have DBF files (or if you copy these files to the machine where the SQL Server is installed) then you don't need to solve remote SQL connection problems but process everything locally.

SSMS is available for download from Microsoft and it does not help with the connection it is just a tool which allows to connect to your SQL engine (SQL Server) installed on any machine and handle the data by SQL commands. And it shows possible errors immediately.  It does not do the data import for you, it does not connect your FoxPro script to SQL Server, it just shows "Yes, the connection is possible". SSMS and/or SQL Server installation contains necessary ODBC drivers obviously and you may reuse them by any other application.

I am not available for hire right now but the script itself should not be difficult to write. (Loop through all DBF records and insert them to existing SQL Server table.)
Oh - SSMS is the management studio - yes, I'm using that.
Can you please give me an example of how to connect to SQL in Foxpro?  A connection string?
Then an insert statement?
I cannot find examples anywhere and I know it must be simple.
I will definitely set up a copy - I guess with robocopy unless you know a better way, to the web server, then set up a Foxpro program there to read Foxpro tables and write to the SQL tables - I'm not taking all the fields over because we don't need them on the web server.
I have a working knowledge of FoxPro, so just a little help and I'll get this done on deadline.
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Love getting the details I need!  I really appreciate the help getting this solved.