Query .DBF file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Query .DBF file

Author

Topic

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2014-05-10 : 09:51:11

Hi all,

I've created a test enviroment for my test case. (Installed Windows 7 x64 and MS SQL Server 2012 x64 on a virtual machine)
I want to query a .DBF file from SQL Server. I use the OPENROWSET function. Installed the "2007 Office System Driver: Data Connectivity Components" and run the following query:

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;

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','dBASE IV;Database=C:\Users\Luuk\Desktop\REL_INFO.DBF','SELECT * FROM REL_INFO.DBF')

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

Read a lot about this problem, but can't figure it out.

Can anybody help me?

Thanks!

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2014-05-10 : 18:45:03

First make sure no other application has that file locked as I've drove myself nuts before with an excel file as it would not query it if I had it open in excel.

Second keep in mind the path is the path on the machine running SQL. Make sure that file exists in that location on your test machine.

Last, I have personally never used open rowser on a dbf so make sure your syntax is correct on it, the table name may be looking for something other than the file name as it does with excel(excel uses the sheet name, not sure if ur syntax is right so google tom review a working version of a dbf query just to double check.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2014-05-12 : 02:36:24

Hi Vinnie881,

Thanks for your reply, however the file is not locked and the path is correct.

Any other ideas?

tnx.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-05-12 : 14:04:26

Did you install the x86 or x64 version of the office system drivers? It appears that you installed the x86 version - and SQL Server is an x64 version and won't be able to call the x86 driver.

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2014-05-13 : 02:36:39

I installed the x64 driver.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources