none
Trouble importing .dbf file into SQL server 2008 express edition

    Question

  • Hey all,

    I'm having trouble exporting my .dbf file into Microsoft SQL server 2008 express edition. I set up an ODBC using a couple different drivers and different versions of the drivers and still got the same error. Below is the error message I am receiving.

     

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

    Could not connect source component.

    Error 0xc02020ff: Source - HOSPSITE [1]: The component "Source - HOSPSITE" (1) was unable to retrieve column information for the SQL command. The following error occurred: ERROR [HYS12] [Microsoft][ODBC dBase Driver] Index file not found.


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

    Pipeline component has returned HRESULT error code 0xC02020FF from a method call. (Microsoft.SqlServer.DTSPipelineWrap)

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

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

    If any one can help that would be greatly appreciated! Thanks!

    Monday, March 22, 2010 1:52 PM

Answers

  • Hi,

    1. You can have look at the below link. This is cleary written that "The SQL Server Import and Export Wizard in SQL Server does not support importing from, or exporting to, dBASE or other DBF files. You can use Microsoft Access or Microsoft Excel to import the data from DBF files into an Access database or Excel spreadsheets, and then use the SQL Server Import and Export Wizard.".

    http://msdn.microsoft.com/en-us/library/aa337084.aspx

    2. Also, try to use openrowset method.

    SELECT * into SomeTable 
    FROM OPENROWSET
    ('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver; 
    SourceDB=\\SomeServer\SomePath\; 
    SourceType=DBF'
    , 
    'SELECT * FROM SomeDBF')

    Before running above query execute this

     

    exec sp_configure 'Ad Hoc Distributed Queries',1
    GO
    RECONFIGURE
    GO

     

     

     

    Hope This helps,

    Chintak.

     

    Tuesday, March 23, 2010 4:55 AM

All replies

  • Hi,

    1. You can have look at the below link. This is cleary written that "The SQL Server Import and Export Wizard in SQL Server does not support importing from, or exporting to, dBASE or other DBF files. You can use Microsoft Access or Microsoft Excel to import the data from DBF files into an Access database or Excel spreadsheets, and then use the SQL Server Import and Export Wizard.".

    http://msdn.microsoft.com/en-us/library/aa337084.aspx

    2. Also, try to use openrowset method.

    SELECT * into SomeTable 
    FROM OPENROWSET
    ('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver; 
    SourceDB=\\SomeServer\SomePath\; 
    SourceType=DBF'
    , 
    'SELECT * FROM SomeDBF')

    Before running above query execute this

     

    exec sp_configure 'Ad Hoc Distributed Queries',1
    GO
    RECONFIGURE
    GO

     

     

     

    Hope This helps,

    Chintak.

     

    Tuesday, March 23, 2010 4:55 AM
  • Hi,

    Openrowset can be used to select data from other sources and then insert this in SQL table.

    for more details you can refer http://msdn.microsoft.com/en-us/library/ms190312.aspx

    You only need to use it when you want to read data from other source then your sql server instance.

    Hope this helps.


    -Chintak
    Friday, December 03, 2010 10:41 PM
  • Hi,

    I have downloaded Microsoft Oledb Provider for Visual Foxpro from this link :

    http://www.microsoft.com/downloads/en/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en

    After installing this data provider, then your SQL Express Management Studio : Import and Export Wizard will display option to directly import or export to Oledb Provider for Visual FoxPro.  It works like charm....  I can import my DBFs in one batch into SQL Express 2008 R2 databases.

     

    Hope this help.


    Ryan Suryanto
    • Proposed as answer by Ryan Suryanto Saturday, January 22, 2011 6:34 PM
    Saturday, January 22, 2011 6:31 PM