none
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
    Wednesday, August 11, 2010 1:00 PM

Answers

  • 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
    • Marked as answer by Naomi NEditor Thursday, August 12, 2010 12:37 PM
    Wednesday, August 11, 2010 5:23 PM
  • 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
    • Marked as answer by Naomi NEditor Thursday, August 12, 2010 12:34 PM
    Thursday, August 12, 2010 9:45 AM
  • 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
    • Marked as answer by Naomi NEditor Wednesday, November 17, 2010 5:11 AM
    Wednesday, November 17, 2010 4:58 AM

All replies

  • 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
    • Marked as answer by Naomi NEditor Thursday, August 12, 2010 12:37 PM
    Wednesday, August 11, 2010 5:23 PM
  • 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
    Wednesday, August 11, 2010 6:24 PM
  • 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
    Thursday, August 12, 2010 4:25 AM
  • 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
    • Marked as answer by Naomi NEditor Thursday, August 12, 2010 12:34 PM
    Thursday, August 12, 2010 9:45 AM
  • 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
    Wednesday, November 17, 2010 4:50 AM
  • 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
    • Marked as answer by Naomi NEditor Wednesday, November 17, 2010 5:11 AM
    Wednesday, November 17, 2010 4:58 AM