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?
Dawn CassaraBusiness/Technology/Marketing ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JesterTooCommented:
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
0
pcelbaCommented:
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.
0
Dawn CassaraBusiness/Technology/Marketing ConsultantAuthor 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?
0
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

pcelbaCommented:
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.)
0
Dawn CassaraBusiness/Technology/Marketing ConsultantAuthor 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.
0
pcelbaCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dawn CassaraBusiness/Technology/Marketing ConsultantAuthor Commented:
Love getting the details I need!  I really appreciate the help getting this solved.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.