Sql-server – “Linked server does not contain table” error when using VFPOLEDB provider linked server

linked-serversql-server-2008

I am receiving the below error when attempting to run any queries via a linked server in SQL Server 2008 R2 using the Visual Fox Prox OLE DB Provider v9.0.0.3504.

I am attempting this as I need to import data from VFP .dbf files into a SQL database.

Error:

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" for linked server "vfp" does not contain the table "pat". The table either does not exist or the current user does not have permissions on that table.

Example Query:

select * from vfp...pat
  • I have ensured that there are 'Full Control' AD permissions to the free table directory for the service account that SQL Server is running and the user account that is logged into SSMS on the server.
  • The server OS is Windows Server 2003 SP2 x86.

  • TSQL for linked server creation is below:

    EXEC master.dbo.sp_addlinkedserver @server = N'VFP', @srvproduct=N'Visual FoxPro', @provider=N'VFPOLEDB', @datasrc=N'"C:\VFP"', @provstr=N'VFPOLEDB.1'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'VFP',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

  • I've also used an alternate @provstr value of 

    Provider =vfpoledb; Data Source =C:\VFP\; Collating Sequence =general;

So my question is have I missed anything that I need to configure? Can anyone provide a few pointers as to what I should check to resolve this problem?

Thanks in advance

EDIT:

I should mention that "allow inprocess" is configured for the VFPOLEDB provider and queries fail when using either 4 part names or OPENQUERY().

Best Answer

Eventually found the answer when attempting to work around the problem using SSIS and the VFP ODBC driver. VFPODBC provided a more verbose error message, stating that the .fpt file was missing for the dbf file I was attempting to query.

The staff that did the original data copy (the VFP data is hosted off server) failed to include all the data available (.cdxs, .fpts, .etc). After recopying the data, the linked server described above functions correctly.

Related Question