Unanswered: Structural changes to linked VFP tables cause connection loss from SQL Server
Our shop supports applications that use data on both SQL Server and Visual FoxPro tables. We have several SQL Server jobs executing stored procedures which reference Visual FoxPro tables through linked-server queries.
Whenever we make structural changes to a FoxPro free table referenced in a linked-server query, or even to a table residing in the same FoxPro database container (DBC) as another table referenced in a linked-server query, attempts to execute such queries return the following error or one similar to it:
Server: Msg 7399, Level 16, State 1, Line 1
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 only way we've been able to re-establish connectivity to the FoxPro tables is to reboot any SQL Servers on which linked servers pointing to the changed FoxPro tables are defined. I've check the 'lazy schema validation' setting for the linked server definition, thinking that perhaps it might have been turned on and thus not checking for schema changes when the queries are run; however, it was set to 'false' (0).
Can anyone shed any light on what causes this problem, and whether there's any way around it without having to reboot SQL Server?