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?
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.