Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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().

share|improve this question
    
There is a little-known bug with VFPOLEDB and VFP ODBC installers. Installing them in reverse order results in the ODBC driver failing repeatedly from Office apps that connect to VFP tables. I haven't tracked the root cause of the problem but because it is installation-based, and repeatable, I can only guess that something is getting "overwritten" improperly. –  Avery Payne Sep 19 '12 at 18:48

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.