Import VFP tables in SQL Server 2008 R2

Question

Hi everybody,

Out of curiosity I tried to import VFP table to SQL Server database using Import/Export Wizard. However, I got errors on the very first steps when I chose VFP OleDB as provider.

From SSMS SQL Server R2 I just wanted to try to import a VFP table using Import/Export Wizard. Got this interesting error on the very first step, though.

See attached.

----------------------
TITLE: SQL Server Import and Export Wizard
------------------------------

The operation could not be completed.

------------------------------
ADDITIONAL INFORMATION:

Feature is not available. (Microsoft OLE DB Provider for Visual FoxPro)

------------------------------
BUTTONS:

OK
------------------------------

===================================

===================================

Feature is not available. (Microsoft OLE DB Provider for Visual FoxPro)

------------------------------
Program Location:

at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetOpenedConnection(WizardInputs wizardInputs, String connEntryName)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnLeavePage(LeavePageEventArgs e)

--------------------------
So, it's possible that VFP tables import is broken with R2?

-------------

Or is it because there is no 64 version available of VFP OleDB?

Should the error be more descriptive?

Thanks in advance.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 1

Hi Naom,

It seems to be data type compatibility. Few years before, i have tried to import data from ms access to SQL server and got lot of issues. So, i have changed data type in SQL server and imported data successfully. I am not sure, but i think import wizard trying to convert VFP data type to suitable SQL data type and failed.

Better export data to text file with | delimiter and try to import database.

---

Ramesh S
www.sequalserver.com

Answer 2

I believe the problem is that I use SQL Server 64 and VFP OleDB is 32. In other words, I can not even go to the next step of selecting the table, I got the error right away after trying to select VFP OleDB.

I believe if this error is expected, it should be better documented rather than giving this weird error.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 3

Other ways of accessing VFP tables (through Linked Server) didn't work either

http://forum.lessthandot.com/viewtopic.php?f=17&t=2861

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 4

Hi Naom,

According to your description, it seems you are using 64-bit SQL Server 2008 R2. Now, you want to import data from Visual Foxpro(VFP) to this SQL Server. If I have misunderstood, please don't hesitate to let me know.

You are right. I tested this scenario before.  There is no 64-bit VFP OleDB provider. That is why we can't import data from VFP to a 64-bit SQL Server. The workaround in the link you provided won't work too, as it is using the VFP provider too. This can't work in 64bit SQL Server.

In order to work around the issue, we can firstly install a 32-bit SQL Server, import the data from VFP to this 32-bit SQL Server, and then synchronize the data from the 32-bit SQL Server to the 64-bit SQL Server.
This workaround is available too in other scenarios that there is no 64-bit provider and we are using 64-bit SQL Server.

If you have any more questions, please feel free to ask.

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 5

Funny :) You seemed to contradict yourself. Luckily you provided today a complete solution and it worked great for me. I hope you don't mind I quoted you in this blog post

Working with 32 bit providers and 64 bit SQL Server

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 6

Naom,

:)

Can I correct my word? I should say we are unable to create link server to VFP in 64-bit SQL Server.

Jin Chen

---

Jin Chen - MSFT

Answer 7

I ran into this problem as well. When you try to hit next to select a table, it errors out immediately. I was using SQL 2008 R2. For me, it turned out to be DEP (Data execution Prevention) on the SQL Server. the vpfoledb driver was written before there was DEP. I hacked the DTWizzard.exe to disable DEP. There are other ways as well. Then I was able to import the data.