Technical Article

How to query dBase .dbf from SQL 2012 64-bit

,

This example shows how I was able to use the 64-bit Microsoft ACE 12.0 provider to query a dBase *.dbf file.

Microsoft Access Database Engine 2010 Redistributable download:

http://www.microsoft.com/download/en/details.aspx?id=13255

Use MyDatabase
GO

sp_configure 'show advanced options', 1
reconfigure 
GO

sp_configure 'Ad Hoc Distributed Queries', 1 
reconfigure
GO


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;

--Must use all CAPS in the query parameter section, except the file name.
SELECT COLUMN1,
COLUMN2
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','dBASE IV;Database=\\RemotServerName\ShareName\Folder1\SubFolder1\','SELECT COLUMN1, COLUMN2 FROM DBASEFIL.dbf WHERE COLUMN3 = ''C''')

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


sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure
GO

sp_configure 'show advanced options', 0
reconfigure 
GO

Rate

4.33 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

4.33 (3)

You rated this post out of 5. Change rating