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

Dawn Cassara
Dawn Cassara used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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?
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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.
So you may use SQL pass-through FoxPro functions: https://msdn.microsoft.com/en-us/library/aa975908(v=vs.71).aspx
http://fox.wikis.com/wc.dll?Wiki~SQLPass-Through

1. SQLCONNECT() or SQLSTRINGCONNECT() to establish a connection to SQL Server (see more: http://fox.wikis.com/wc.dll?Wiki~ConnectionString, you just need to provide values from the first table)
2. Above function returns connection handle which you'll use for other function calls
3. Then you may call SQLEXEC() to send SQL commands to the server:
lnH = SQLSTRINGCONNECT("your conn string")
IF lnH <= 0
  *-- process error here
  RETURN
ENDIF
lnResult = SQLEXEC(lnH, "USE YourDatabase")
IF lnResult < 0
  *-- process error here
  SQLDISCONNECT(lnH)
  RETURN
ENDIF

*-- Exporting table columns ID, column1 - 3 to the table on SQL Server having the same structure (column names)
SELECT 0
USE SomeTable
SCAN ALL
  lnResult = SQLEXEC(lnH, "INSERT INTO dbo.sqlTableName (Id, column1, column2, column3) VALUES (?Id, ?column1, ?column2, ?column3)")
  IF lnResult < 0
    *-- Process error here
  ENDIF 
ENDSCAN

Open in new window

Of course, you may update above code to handle UPDATEs of existing data on SQL Server. Also obvious is to build the SQL command in a variable and then use the variable in SQLEXEC. You may also test all above commands in the FoxPro Command Window etc. etc.

The code to process errors can do following:
1. Call AERROR()
2. Display the full error info
3. Exit the program

If you are able to connect the SQL Server on another computer the do it this way instead of copying the DBF files but that depends on your decision. FoxPro help also provides valuable information about SQL pass-through functions.
Dawn CassaraBusiness/Technology/Marketing Consultant

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial