We help IT Professionals succeed at work.

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

Dawn Cassara
Dawn Cassara asked
on
311 Views
Last Modified: 2018-05-09
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?
Comment
Watch Question

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

Commented:
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.
Dawn CassaraBusiness/Technology/Marketing Consultant

Author

Commented:
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?
CERTIFIED EXPERT

Commented:
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.)
Dawn CassaraBusiness/Technology/Marketing Consultant

Author

Commented:
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.
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Dawn CassaraBusiness/Technology/Marketing Consultant

Author

Commented:
Love getting the details I need!  I really appreciate the help getting this solved.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions