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?
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?
FoxPro
Last Comment
Dawn Cassara
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.
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.
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?
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.)
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.)
ASKER
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Love getting the details I need! I really appreciate the help getting this solved.
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