0

I have to read two .dbf files that came from a VFP server in SQL and manipulate them. They are sitting in a folder on the server. First, I have installed the VFPOLEDB driver, the ODBC driver, and its update. I have successfully created a linked server to the free tables, and tested it in SQL and it says the connection is fine and there's stuff there. I still can't seem to even look at the files. Anyone see anything wrong with my code? Or any tips? Sample code of known working code? I have found a lot of examples online, but when I apply with the many hours I put into modifying them, they still don't work.

Here is my code:

Select * From OPENQUERY(LINKEDSVR, 'Select * from employee.dbf')

LINKEDSVR parameter info:

Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: Microsoft Visual FoxPro Driver
Data source: linkedfoxpro

ODBC linkedfoxpro config:

Datasource name: linkedfoxpro
databasetype: Free Table Directory
Path: c:\somefolder

Driver: Microsoft FoxPro VFP Drver (*.dbf)

Thanks!

Dave M
  • 1,302
  • 1
  • 16
  • 28
Marrs
  • 706
  • 7
  • 10
  • Oh and the error I get form doing executing this query: which makes kind of sense, but the dbf file also has all its "constructor tables and index's" from its foxpro server in the same folder: OLE DB provider "MSDASQL" for linked server "LINKEDSVR" returned message "[Microsoft][ODBC Visual FoxPro Driver]Index does not match the table. Delete the index file and re-create the index.". Msg 7350, Level 16, State 2, Line 2 Cannot get the column information from OLE DB provider "MSDASQL" for linked server "LINKEDSVR". – Marrs Oct 08 '11 at 11:23
  • For those of you stumped on this problem as well, after many hours I randomly thought of a fix that for some reason worked. . . I deleted the .CDX file that SQL generates from the FoxPro files. Then I reopened SQL, and it started working with the same code posted above!.... Problem solved! If anyone can explain this to me why this happened please do! * NOTE: these FoxPro files never have there data/index's altered. – Marrs Oct 08 '11 at 16:25

1 Answers1

2

Those CDX files are not generated by SQL - that's the index file created/used by VFP. They are very prone to corruption so it is likely that the CDX was already corrupted when you got it or in transit.

By deleting the CDX file, you have removed all indexes from the table and also the source of the error.

Sam
  • 1,509
  • 3
  • 19
  • 28
William Mioch
  • 917
  • 9
  • 21