Querying DBF from SQL Server 2008-2012

Question

I've the following problem, we have an old fox pro app running in an SCO UNIX server (I know this is amazing!), we actually transfer files from FTP to differents server then we transfor them from dbf to a txt and then! we read the txts and we finally save them in a SQL.

The above situation is awfull i know, now we have a lot of new apps, but this in particular cause us a lot of problems, and we are buying a new solution, but meanwhile, we need to deal with this.

So we have SQL 2008 Servers and SQL 2012 Servers, and i want to read and modify the DBF directly from the SQL Servers, so I google it and get the following solution.

sp_configure 'show advanced options', 1
reconfigure
GO

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1;
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1;

SELECT COLUMN1,
COLUMN2
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','dBASE III;Database=\\RemotServerName\ShareName\Folder1\SubFolder1\','SELECT COLUMN1, COLUMN2 FROM DBASEFIL.dbf WHERE COLUMN3 = ''C''')

I Almost cry, but then i get the following error (I translate it from spanish):

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Mens. 7303, Level 16, State 1, Line 5
Can not initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I Hope you could give me some advice, i tried a lot of solutions with out any result

Answer 1

Try creating a Linked Server in SQL Server to the Foxpro database.

Answer 2

I reached that 7303 problem trying to do the same thing and it was easy to fix. Just make sure the SQL user account has the right permissions to the files you're trying to import.