SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Query .DBF file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Luuk123
Starting Member

49 Posts

Posted - 05/10/2014 :  09:51:11  Show Profile  Reply with Quote
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!

Edited by - Luuk123 on 05/10/2014 09:51:58

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 05/10/2014 :  18:45:03  Show Profile  Reply with Quote
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
Go to Top of Page

Luuk123
Starting Member

49 Posts

Posted - 05/12/2014 :  02:36:24  Show Profile  Reply with Quote
Hi Vinnie881,

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

Any other ideas?

tnx.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
797 Posts

Posted - 05/12/2014 :  14:04:26  Show Profile  Reply with Quote
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.
Go to Top of Page

Luuk123
Starting Member

49 Posts

Posted - 05/13/2014 :  02:36:39  Show Profile  Reply with Quote
I installed the x64 driver.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000