"VFPOLEDB" for linked server reported an error. Access denied

Hi Cindy,
After hours (days?) researching this, your name keeps popping up, you must be the GOTO person for VFP. So:

We have a server that runs a VFP application and stores .dbf free tables in a directory shared as F:\apps\tele.

I am working on an XPPro box running SQL Server Express 2005 using Management Studio Express.

I am logged on as a user with administrator rights for the domain

We would like to be able to query this data and move it to our SQL tables as needed.

Here is what I have:

EXEC sp_addlinkedserver @.server = 'tele',
@.srvproduct = 'VFP',
@.provider = 'VFPOLEDB',
@.datasrc = 'F:\apps\tele',
@.provstr = 'VFPOLEDB.1'
GO

SELECT * FROM tele...PROSPECT_DIVU

Here is what I get:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" for linked server "tele" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "VFPOLEDB" for linked server "tele".

I have tried:

exec sp_addlinkedsrvlogin
@.rmtsrvname = 'tele',
@.useself = false,
@.locallogin = NULL,
@.rmtuser = 'admin',
@.rmtpassword = NULL
go
Same result.

I have also tried:

EXEC sp_addlinkedserver
'tele2',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'F:\apps\tele',
NULL,
'dBase IV'
GO

When I run:

select * from tele2...prospect_divu

I get:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "tele2" returned message "The Microsoft Jet database engine could not find the object 'prospect_divu'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "tele2". The provider supports the interface, but returns a failure code when it is used.

What would you suggest?

FOR SQL MSExpress

In object explored drill down

Server Objects

Linked Servers

Providers

VFPOLEDB

Right Click Select Properties

Check "Allow Inprocess"

Click OK

DONE

|||

Hi JC,

I've created a new thread with your post. Here's some content I've posted several times in the NNTP groups:

"What permissions does the SQL Server local system account have? I'm ok on my laptop with the SQL Server started with "local system account" and my Windows login being MachineName\Cindy. When I tried against my SQL Express instance which was started using the "NT AUTHORITY\NetworkService" I had the same errors you report. "

Basically, the account the SQL Server runs under needs to have permissions to the directory where the DBFs are stored.

Your error with Jet may stem from the fact that the structure of Fox DBFs has changed over time and the changes aren't compatible with the dBase IV format that Jet recognizes.

|||In addition to Cindys recommondation I would add that you try to use a common query to identity if the connection is working to the dbf file. I am not familiar with the dbf file schema, but there could be an object (table etc.) which always exists in the schema which you could query to eliminate other problems (like in SQL Server the sysobjects object). I also guess that the problem is based upon file access security. Depengin on the settings of the linked server you connect with the "users context" or with SQL server service account security (which can be in some case not priviledged anough to access / modify the datafiles.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi

I am still fighting with this as well. But one thing I have noticed "Select *" it does not like, "Select col1, col1, col3" it is ok with and the error I was get with "*" was the access denyed. You will probably notice that in addition to the Messages tab with the errors that you also have a Results tab with only the column headings.

Other then that I am still having issues connecting to a UNC as the data source. Like \\dispatch\dispatchtables\

Hope it helps you some.

Paul