Error Importing VisualFoxPro Table

Question

Error Importing VisualFoxPro Table

John McLaughlin-213204

Old Hand

Points: 340
More actions
April 25, 2005 at 12:30 pm

#85959

Hi,
When using the OPENROWSET function, I occasionally get the following error while trying to import a VFP table.
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Error reading file.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005:
The workaround has been to stop and start SQL Server, which leads me to believe that SQL Server is caching information about the table. Once I stop and restart SQL the table imports with no problem. I am looking for a way to remove the cached information so I do not have to stop and re-start SQL Server. I have tried DBCC FREEPROCCACHE (although I didn't think it would work). If anyone has any experience/insight into this type of problem, I would appreciate hearing about it.
Thanks
John

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Curtis Smith Hall of Fame Points: 3063 More actions · Answer 1

have you tried DBCC DROPCLEANBUFFERS. Can't remember, but I think you need to a checkpoint just before because this command only clears the "clean" buffers.

Curtis Smith
SQL Server DBA
Well in worked in Theory ...

John McLaughlin-213204 Old Hand Points: 340 More actions · Answer 2

I haven't yet, but I will the next time it happens. Thanks for the suggestion.

John

John McLaughlin-213204 Old Hand Points: 340 More actions · Answer 3

DBCC DROPCLEANBUFFERS didn't work, but I found that switching providers did as follows.

New: Works when original doesn't.

SELECT * FROM OPENROWSET("vfpoledb","\\wsipc-svr8\INTCALC\CALCULATOR\DRIVER_DBC\";"";"","SELECT DISTINCT * FROM CORPACHIST WHERE TraDate = {04/22/2005} AND Product_Id IN(107)")

I still don't know why using MSDASQL works sometimes and not others but maybe someone with knowledge of the internal workings of ODBC/OLEDB drivers can expand on that.

Original: Works most of the time.

SELECT * FROM OPENROWSET("MSDASQL","Driver=Microsoft Visual FoxPro Driver; SourceType=dbf; SourceDB=\\wsipc-svr8\INTCALC\CALCULATOR\DRIVER_DBC\","SELECT DISTINCT * FROM CORPACHIST WHERE TraDate = {04/22/2005} AND Product_Id IN(107)")