3

This seems like it should be easy enough, but I'm having trouble in what feels like the final stretch..

I want to connect to a Fox Pro .dbf file

1.) I've applied the following changes on my local SQL instance. All the code was found via various google results (I'm not an expert on this at all)

USE [master] 
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'VFPOLEDB', N'AllowInProcess', 1 
RECONFIGURE
GO

EXEC master.dbo.sp_MSset_oledb_prop N'VFPOLEDB' , N'DynamicParameters' , 1
RECONFIGURE
GO

2.) When I run the following (which by the sound of it, is where the magic should happen)

select * from 
    openrowset('VFPOLEDB','\\path_segment\path_segment\clock.dbf';'';'','SELECT * FROM clock')

I get an error..

OLE DB provider 'VFPOLEDB' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

... and that's basically where my google skills end, any ideas on what I can do to get the above working?

Rohan Büchner
  • 5,333
  • 4
  • 62
  • 106
  • Are you using 64 bit SQL Server? VFPOLEDB provider is 32 bit so it can not be used as an in process server from a 64 bit program. – Mikael Eriksson Jan 26 '12 at 10:21
  • Yes i am... hmm, any work-arounds? Or know of any other options that would enable me to get access to the data? – Rohan Büchner Jan 26 '12 at 10:35
  • If this is a one time thing you can use the 32 bit version of `SQL Server Import and Export Wizard`. – Mikael Eriksson Jan 26 '12 at 13:45
  • We have a biometric device that saves its data to the fox pro / .dbf files... id like to automate the reporting, at the moment they have to export and copy and paste it into an existing excel file and update the charts from there on. I'f i can get the data in sql we can use SSRS for the reports. Well that's the idea atleast. :P – Rohan Büchner Jan 26 '12 at 14:29

1 Answers1

2

Not sure if it can help, but I posted an answer for this thread for someone trying to deal with converting VFP tables to SQL. The starting point I offered the person appeared to have him on the right track.

It deals with linked server, using the VFP OleDB driver (ensure you have the latest version). The connection string should point to the PATH where the .dbf files are located, then query from them by their name (you do not explicitly need the .dbf suffix) for your querying.

Community
  • 1
  • 1
DRapp
  • 47,638
  • 12
  • 72
  • 142