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!

Answer 1

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.

 

Answer 2

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

Answer 3

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