Import DBF files into Sql Server

Question

I need a little help figuring this out because I'm new to stored procedures. I am trying to import a .DBF table into Sql Server 2008 using this store procedure.

CREATE PROCEDURE spImportDB
-- Add the parameters for the stored procedure here

AS
BEGIN

-- Insert statements for procedure here

 SELECT * into Products
 FROM OPENROWSET('vfpoledb','C:\Users\Admin\Doc\Data\DBF',
 'SELECT * FROM MyTable')

END
GO

I receive this error. The OLE DB provider "vfpoledb" has not been registered.This isn't true, I've installed it and it works fine in my other application.

I've also tried running it this way with this provider but I receive this error message Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

CREATE PROCEDURE spImportDB
-- Add the parameters for the stored procedure here

AS
BEGIN

-- Insert statements for procedure here

 SELECT * into Products
 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\Users\Admin\Doc\Data\DBF',
 'SELECT * FROM MyTable')

END
GO

What's the easiest way to create this stored procedure? I want it to be a stored procedure not a wizard or program so please don't give me any programs.

Answer 1

I'm not sure about the "friendly-name" for VFPOLEDB, but the second SP should work (i.e. using 'Microsoft.Jet.OLEDB.4.0') as long as you're pointing to a specific DBF file by name. It looks like you're pointing to a directory, not the actual file.

Further information may be found in: http://msdn.microsoft.com/en-us/library/ms190312.aspx

Answer 2

You can try

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

from this previous question